if exists(select 1 from tb where col - floor(col) <> 0 ) print '不是int型' else print '是int型'
if exists(select 1 from tb where test - floor(test) <> 0 ) print '不是int型' else print '是int型'
ISNUMERIC(expression) 判断是不是数值
哈哈,忘了说了,test是varchar(50),保存的数据可能是纯int,也可能是汉字等
select s.name,t.name from syscolumns s,systypes t where s.xtype= t.xtype and name = 'test' and id = object_id('ta')
select type_name(user_type_id) from sys.all_columns where object_id=object_id('BoysNames') and name ='id'
ISNUMERIC ( expression ) ISNUMERIC ( expression )
那就麻烦了,用ISNUMERIC判断.ISNUMERIC 确定表达式是否为一个有效的数字类型。语法 ISNUMERIC ( expression )参数 expression要计算的表达式。返回类型 int注释 当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例 A. 使用 ISNUMERIC 下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs SELECT ISNUMERIC(zip) FROM authors GOB. 使用 ISNUMERIC 和 SUBSTRING 下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。USE pubs GO -- Because the title column is all character data, expect a result of 0 -- for the ISNUMERIC function. SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title) FROM titles GO下面是结果集:type price --------------- -------------------------- ----------- The Busy Execut 19.99 0 Cooking with Co 11.95 0 You Can Combat 2.99 0 Straight Talk A 19.99 0 Silicon Valley 19.99 0 The Gourmet Mic 2.99 0 The Psychology (null) 0 But Is It User 22.95 0 Secrets of Sili 20.00 0 Net Etiquette (null) 0 Computer Phobic 21.59 0 Is Anger the En 10.95 0 Life Without Fe 7.00 0 Prolonged Data 19.99 0 Emotional Secur 7.99 0 Onions, Leeks, 20.95 0 Fifty Years in 11.95 0 Sushi, Anyone? 14.99 0 (18 row(s) affected)
select type_name(user_type_id) from sys.all_columns where object_id=object_id('BoysNames')--表名 and name ='id'--列名
happyflystone: name不明确,即使明确了,也不是我要的东东啊
SELECT o.name AS obj_name, c.name AS col_name, TYPE_NAME(c.user_type_id) AS type_name FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id = c.object_id WHERE o.name = ?--table_name ORDER BY col_name; GO
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 行)*/
少s.那是一开始你的问题不明确ISNUMERIC ( expression )
IF(SELECT DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME='表名' AND COLUMN_NAME='test')='int' BEGIN --test列是int型 END
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*/
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 行受影响)
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) */
4 -3232 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 @aid 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 行受影响)
id a res ----------- -------------------- ----- 1 NULL True
SELECT id, a,CASE WHEN patindex('%[^-0-9]%',isnull(a,'s'))>0 THEN 'False' ELSE 'True' END res FROM @a
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*/
print '不是int型'
else
print '是int型'
print '不是int型'
else
print '是int型'
判断是不是数值
where s.xtype= t.xtype and name = 'test' and id = object_id('ta')
and name ='id'
ISNUMERIC ( expression )
确定表达式是否为一个有效的数字类型。语法
ISNUMERIC ( expression )参数
expression要计算的表达式。返回类型
int注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例
A. 使用 ISNUMERIC
下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs
SELECT ISNUMERIC(zip)
FROM authors
GOB. 使用 ISNUMERIC 和 SUBSTRING
下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。USE pubs
GO
-- Because the title column is all character data, expect a result of 0
-- for the ISNUMERIC function.
SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title)
FROM titles
GO下面是结果集:type price
--------------- -------------------------- -----------
The Busy Execut 19.99 0
Cooking with Co 11.95 0
You Can Combat 2.99 0
Straight Talk A 19.99 0
Silicon Valley 19.99 0
The Gourmet Mic 2.99 0
The Psychology (null) 0
But Is It User 22.95 0
Secrets of Sili 20.00 0
Net Etiquette (null) 0
Computer Phobic 21.59 0
Is Anger the En 10.95 0
Life Without Fe 7.00 0
Prolonged Data 19.99 0
Emotional Secur 7.99 0
Onions, Leeks, 20.95 0
Fifty Years in 11.95 0
Sushi, Anyone? 14.99 0 (18 row(s) affected)
and name ='id'--列名
name不明确,即使明确了,也不是我要的东东啊
TYPE_NAME(c.user_type_id) AS type_name
FROM sys.objects AS o
JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE o.name = ?--table_name
ORDER BY col_name;
GO
可能是我说的有歧义,我是问,用正则的方法有哪些?有函数的方法有哪些?
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 行)*/
少s.那是一开始你的问题不明确ISNUMERIC ( expression )
IF(SELECT DATA_TYPE FROM information_schema.columns
WHERE TABLE_NAME='表名' AND COLUMN_NAME='test')='int'
BEGIN
--test列是int型
END
--用isnumeric
print isnumeric('1')
print isnumeric('1.01')
print isnumeric('A101')
print isnumeric('楼主')--结果
/*
1
1
0
0*/
想要判断它保存的数据是不是纯int的。比如25就是,2.5就不是
print 'int'
else
print 'not int'if (isnumeric('1') = 1 and charindex('.','1') = 0)
print 'int'
else
print 'not int'--结果
/*
not int
int*/
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 行受影响)
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)
*/
????
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 @aid 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 行受影响)
----------- -------------------- -----
1 NULL True
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*/