判断字段是否为数字

[jidianxueyuan]

判断字段是否为数字 是数字把数据返回，不是的话返回0

[mjf_2008]

select case when col like '%[0-9]%' then 1 else 0 end 
from 
(
select 'aaa' as col union all
select '111'union all
select '4' union all
select '4aaa'
) a

--这个方法貌似不行吧。。

[luoyoumou]

-- isnumeric() 正解!

[luoyoumou]

--判断字段是否为数字 是数字把数据返回，不是的话返回0
select (case when isnumeric(colname)=1 the colname else 0 end) as is_num
from tb;

[luoyoumou]

--判断字段是否为数字 是数字把数据返回，不是的话返回0
drop table tb;
create table tb(colname varchar(10));
insert into tb(colname)
select
'luoyoumou' union all select
'123' union all select
'123ab4';

select (case when isnumeric(colname)=1 then colname else 0 end) as is_num
from tb;
--------------------------------------------------------
0
123
0

[忆轩辕]

[Quote=引用 11 楼 mjf_2008 的回复:]
SQL codeselectcasewhen collike'%[0-9]%'then1else0endfrom
(select'aaa'as colunionallselect'111'unionallselect'4'unionallselect'4aaa'
) a

--这个方法貌似不行吧。。
[/Quote]

不要只顾拿了代码直接执行,这句改一个地方就可以实现了

[ChinaJiaBing]

---返回1或0(数据型)
select isnumeric(1)
--返回1或0（表示是不是日期型）
select isdate(1)

[wly0502]

SQL code

select z_ww from tbl_user where patindex('%[0-9]%',zww)>0

[chen8410]

ISNUMERIC ( expression )
确定表达式是否为有效的数值类型。

如果输入表达式的计算值为有效的整数、浮点数、money 或 decimal 类型时，ISNUMERIC 返回 1；否则返回 0。返回值为 1 时，指示可将 expression 至少转换为上述数值类型中的一种。

[zhengduan964532]

SELECT CASE WHEN ISNUMERIC(TYPE)=0 THEN 1 ELSE 0 END 
 FROM A 

[laker_914]

isnumeric() 

[水族杰纶]

select case when ISNUMERIC(col)=1 then col else 0 end 
from tb

[chuifengde]

select case when isnumeric([Field])=1 then [Field] else 0 end 
from [Table]

[SQL77]

SELECT CASE WHEN ISNUMERIC(COL)=0 THEN 0 ELSE COL END AS COL FROM TB

[忆轩辕]

if @a like '%[0-9]%'

[--小F--]

select 
   case when isnumeric(col)=1 then col else 0 end 
from 
  tb

[chuifengde]

or

select isnull(nullif(isnumeric([Field]),1),[Field])
from [Table]

[忆轩辕]

看成变量了..orz

select case when col like '%[0-9]%' then 1 else 0 end from tb

--竟然忘了还有isnumeric........

[fwacky]

select case when col like '%[0-9]%' then 1 else 0 end 
from 
(
select 'aaa' as col union all
select '111'union all
select '4'
) a

=======
0
1
1

[fwacky]

select case when isnumeric(col) = 1 then 1 else 0 end 
from 
(
select 'aaa' as col union all
select '111'union all
select '4'
) a
===
======= 
0 
1 
1
--两个都行！

[yangyn77]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselectcasewhenISNUMERIC(col)=1then colelse0endfrom tb
[/Quote]
支持