我们在SQL中经常要判断字符串字段中的内容能否转为数值，经过网络搜索、借鉴大咖的写法，再结合的自己的想法，写下这一篇文章备忘
测试数据
/*************************************
* 测试数据 *
* 测试环境：SQL2008r2 *
* 测试日期：2018-03-27 *
*************************************/
declare @test table
(
ID nvarchar(10)
)
insert into @test(ID)
values
('+123'), ('-8'), ('12-'), ('12345'), ('12x45'), ('+12-45'), ('-12.45'),
('+12.45'), ('123456'), ('12345z'), ('798'), ('7'), ('1280'), ('xy45'),
('xyz'), ('17'), ('34'), (N'电视'), ('123\45'), ('3.5'), ('4.8.5'), (''), (null),
('$'), ('2D3'), ('1e1')
select * from @test order by ID一、纯数字 (不支持正负数，小数点)
/*************************************
* 一、纯数字 (不支持正负数，小数点) *
*************************************/
-- 1. not like 写法
select *
select *
from @test
where 1=1
and id not like '%[^0-9]%' -- 纯数字（排除0~9以外字符）
and isnull(ID, '') <> '' -- 排除null和空字符串
order by ID
-- 2. patindex 写法
select *
from @test
where 1=1
and patindex('%[^0-9]%', ID) = 0 -- 纯数字（排除0~9以外字符）
and isnull(ID, '') <> '' -- 排除null和空字符串
order by ID
二、纯数值 (支持正负数，小数点)
/***********************************
* 二、纯数值 (支持正负数，小数点) *
***********************************/
-- 1. isnumeric()函数
select *
from @test
where isnumeric(ID) = 1
order by ID
-- 注意：isnumeric()函数有个缺点！
-- select
-- ,isnumeric('-') as '-' --1
-- ,isnumeric('+') as '+' --1
-- ,isnumeric('$') as '$' --1
-- ,isnumeric('.') as '.' --1
-- ,isnumeric(',') as ',' --1
-- ,isnumeric('\') as '\' --1
-- ,isnumeric('2D3') AS '2D3'--1
-- ,isnumeric('1d1') AS '1d1'--1
-- ,isnumeric('1e1') AS '1e1'--1
-- ,isnumeric('d') AS 'd' --0
--当含有美元符、加减号、逗号等符号时，或者D、E的前后均出现数字时，也会返回1，这就比较头疼了。
--标点符号倒是好理解，为什么d,e这种情况，真是不理解微软设计的意图。
-- 2. isnumeric() 函数加强版 (推荐)
select *
from @test
where isnumeric(ID) = 1
-- 不能包含0~9、+、-、.以外字符 (注意：负号(-)必须要最前或最後, 否则负号不生效)
and id not like '%[^0-9|+|.|-]%'
order by ID
-- 3. not like 写法
select *
from @test
where 1=1
-- 不能包含0~9、+、-、.以外字符 (注意：负号(-)必须要最前或最後, 否则负号不生效)
and id not like '%[^0-9|+|.|-]%'
-- 正号(+)、负号(-)的必须放在前面
and (charindex('+', ID) <= 1 and charindex('-', ID) <= 1)
-- 小数点(.)只能出现1次
and len(id)-len(replace(id, '.', '')) <= 1
-- 排除null和空字符串
and isnull(ID, '') <> ''
order by ID
-- 4. patindex 写法
select *
from @test
where 1=1
-- 不能包含0~9、+、-、.以外字符 (注意：负号(-)必须要最前或最後, 否则负号不生效)
and patindex('%[^0-9|+|.|-]%', ID) =0
-- 正号(+)、负号(-)的必须放在前面
and (charindex('+', ID) <= 1 and charindex('-', ID) <= 1)
-- 小数点(.)只能出现1次
and len(id)-len(replace(id, '.', '')) <= 1
-- 排除null和空字符串
and isnull(ID, '') <> ''
order by ID
三、Like其它常用写法
/********************************
* 三、Like其它常用写法 *
********************************/
select *
from @test
where 1=1
--and id like '%[^0-9]%' -- 有包含0~9以外的字符
--and id not like '%[^0-9]%' -- 纯数字（排除0~9以外字符）
--and id like '%[x]%' -- 包含x
--and id like '%[0-9]%' -- 包含0~9
--and id like '%[+,-,.]%' -- 包含+、-、.
--and id not like '%[+,-,.]%' -- 不包+、-、.
--and id like '[+,-]%' -- 以+、-开头
--and id like '[^+,^-]%' -- 排除+、-开头
--and id like '[0-9]%' -- 0~9开头
--and id like '[0-9]%[0-9]' -- 0~9开头，0~9结尾(两位或以上字符串）
--and id like '[0-9]%[^A-Z]' -- 以[0~9]开头，不以[A~Z]结尾
--and id like '%[0-9]' -- 0~9结尾
--and id like '__45' -- 以45结尾, 长度为4 (_为占位符)
--and id like '12_45' -- 12开头, 45结尾, 长度为5 (_为占位符)
order by ID