Converting Text Field to Number

1

I'm very new to writing sql queries. I'm trying to convert a text field to numeric so I can work with it in another program. This is the snippet I'm trying to work with. I keep getting error messages. The field I want as numeric is CNS_USRFLD_107

SELECT
REM_PID AS PID,
--SUBSTRING (rm.REM_PIN, 1,12) AS GPIN,
REM_PIN AS CAMA_GPIN,
CASE WHEN CAST(CNS_USRFLD_107 as numeric)
CONSTRRES.CNS_USRFLD_107 AS RESIDENTIAL_SUBDIVISION_ADJUST_PER,
GIS_EXT.GIS.TAX_PARCEL.SHAPE
FROM
VISION_DEV.Vision.Real_Prop.REALMAST
INNER JOIN
VISION_DEV.Vision.real_Prop.CONSTRRES
ON
REM_PID = CNS_PID

2

you have to understand

the text data how it is
and the numeric data type

only if both of them match then error will not come

3

Does my query look correct when using the Cast function?

4

Welcome,

Look at this test harness on what you are trying to do. Can you see what the issue might be?

declare @disney table(CNS_USRFLD_107 varchar(50))

insert into @disney
select 'Mickey Mouse' union
select '2022'


select TRY_CAST(CNS_USRFLD_107 as numeric), CNS_USRFLD_107 From @disney
5

your query looks fine

but data

text = 'Hello' will give error = cast as numeric
text = '3456' will work = cast as numeric