判断纯数值字符串

我们在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