34,575
社区成员
发帖
与我相关
我的任务
分享
declare @a table(id INT identity(1,1),a varchar(20))
INSERT @a SELECT NULL
UNION ALL SELECT '2342'
UNION ALL SELECT '323.532'
UNION ALL SELECT '-3232'
UNION ALL SELECT 0
UNION ALL SELECT '23--342'
UNION ALL SELECT 'asdf234'
UNION ALL SELECT '123.00'
-- 如果考虑正负也的考虑第一位的正负号
SELECT id, a,CASE WHEN patindex('[^-]%[^0-9]%',a)>0 THEN 'False' ELSE 'True' END FROM @a
/* 结果
id a
----------- -------------------- -----
1 NULL True
2 2342 True
3 323.532 False
4 -3232 True
5 0 True
6 23--342 False
7 asdf234 False
8 123.00 False
*/
declare @a table(id INT identity(1,1),a varchar(20))
INSERT @a SELECT NULL
UNION ALL SELECT '2342'
UNION ALL SELECT '323.532'
UNION ALL SELECT '-3232'
UNION ALL SELECT '0'
UNION ALL SELECT '23342'
UNION ALL SELECT 'asdf234'
SELECT id, a,CASE WHEN patindex('%[^-0-9]%',a)>0 THEN 'False' ELSE 'True' END res FROM @a
id a res
----------- -------------------- -----
1 NULL True
2 2342 True
3 323.532 False
4 -3232 True
5 0 True
6 23342 True
7 asdf234 False
(7 行受影响)
declare @a table(id INT identity(1,1),a varchar(20))
INSERT @a SELECT NULL
UNION ALL SELECT '2342'
UNION ALL SELECT '323.532'
UNION ALL SELECT '-3232'
UNION ALL SELECT 0
UNION ALL SELECT '23342'
UNION ALL SELECT 'asdf234'
SELECT id, a,CASE WHEN patindex('%[^0-9]%',a)>0 THEN 'False' ELSE 'True' END res FROM @a
/* 结果
id a res
----------- -------------------- -----
1 NULL True
2 2342 True
3 323.532 False
4 -3232 False
5 0 True
6 23342 True
7 asdf234 False
(7 row(s) affected)
*/
declare @a table(id INT identity(1,1),a varchar(20))
INSERT @a SELECT NULL
UNION ALL SELECT '2342'
UNION ALL SELECT '323.532'
UNION ALL SELECT '-3232'
UNION ALL SELECT '0'
UNION ALL SELECT '23342'
UNION ALL SELECT 'asdf234'
select *,case when isnumeric(a) =1 and charindex('.',a)=0 then '是' else '否' end
from @a
(7 行受影响)
id a
----------- -------------------- ----
1 NULL 否
2 2342 是
3 323.532 否
4 -3232 是
5 0 是
6 23342 是
7 asdf234 否
(7 行受影响)
if (isnumeric('1.01') = 1 and charindex('.','1.01') = 0)
print 'int'
else
print 'not int'
if (isnumeric('1') = 1 and charindex('.','1') = 0)
print 'int'
else
print 'not int'
--结果
/*
not int
int
*/
--用isnumeric
print isnumeric('1')
print isnumeric('1.01')
print isnumeric('A101')
print isnumeric('楼主')
--结果
/*
1
1
0
0
*/
IF(SELECT DATA_TYPE FROM information_schema.columns
WHERE TABLE_NAME='表名' AND COLUMN_NAME='test')='int'
BEGIN
--test列是int型
END
declare @a table(id INT identity(1,1),a varchar(20))
INSERT @a SELECT NULL
UNION ALL SELECT '2342'
UNION ALL SELECT '323.532'
UNION ALL SELECT '-3232'
UNION ALL SELECT 0
UNION ALL SELECT '23342'
UNION ALL SELECT 'asdf234'
SELECT id, CASE WHEN ISnumeric(a)=1 and CEILING(a)=a THEN 'True' ELSE 'False' END res FROM @a
--result
/*id res
----------- -----
1 False
2 True
3 False
4 True
5 True
6 True
7 False
(所影响的行数为 7 行)*/