SQL SERVER 2012 T-SQL 新增函数-TRY_CONVERT，非常耗用。

看一个例子：

[sql]

declare @numerics as table

( row_id tinyint identity(1,1) primary key, numeric_val varchar(100))

insert into @numerics(numeric_val)values ('35.2'),(' 100.7(received tylenol per RN'),(' 120'),('100.6(@4pm)'),('92.4 orally'),('37.2(99.0)'),('35.3 C'),('-97.6')

www.2cto.com

select n.numeric_val, try_convert(float, n.numeric_val) as nv_try_convert, isnumeric(n.numeric_val) as nv_isnumeric, case

when try_convert(float, n.numeric_val) is not null

then abs(try_convert(float, n.numeric_val))

when left(ltrim(n.numeric_val),4) like '[0-9][0-9].[0-9]'

then abs(try_convert(float, left(ltrim(n.numeric_val),4)))

when left(ltrim(n.numeric_val),5) like '[0-9][0-9][0-9].[0-9]'

then abs(try_convert(float, left(ltrim(n.numeric_val),5))) end as nv_try_convert_case

from @numerics n

作者 incognito007