SQLTeam.com | Weblogs | Forums

Converting Text Field to Number

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

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

Does my query look correct when using the Cast function?

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


your query looks fine

but data

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