SQL - Try_parse()

TRY_PARSE does two things - parse text using a specific culture and return NULL if the cast fails.


e.g.


with tbl as(
  select distinct api, ltrim(rtrim([GL/Ground Level/Elevation_above_MSL])) as GL, ltrim(rtrim(kb)) as KB
  FROM .[dbo].[tbl_NAWAT_COMPL_DirectionlSurvey] with (nolock)
  where kb is not null and kb <> 'NULL' and api is not null
 )

 select API, GL, TRY_PARSE(KB as float) as KB
 from(
select api, GL,
case when right(KB,4) = 'feet' then ltrim(rtrim(left(KB,len(KB)-4)))
when replace(KB,' ','') like '%@%usft%' then  left(right(KB, len(KB)-charindex('@',KB)),charindex('usft',right(KB, len(KB)-charindex('@',KB)-1)))
else KB
end as KB
from tbl
)d

Here I use try_parse because I only have two cases, but there are also characters beyond these two cases. since try_parse will return null for failed cases, I don't need to worry other cases.