这个可以,应该是楼主没弄好的原因, SELECT * FROM TB WHERE ISNUMERI(COL)=1 然后子查询再查,如果后面加条件应该也可以,不过不能加在前面
select isnumeric('342142treter314')返回结果为0
DECLARE @TB TABLE(ID VARCHAR(10)) INSERT @TB SELECT 'A' UNION ALL SELECT 2 SELECT * FROM @TB WHERE ISNUMERIC(ID)=1 AND ID=2 /*(所影响的行数为 2 行)ID ---------- 2(所影响的行数为 1 行)*/ SELECT * FROM @TB WHERE ID=2 AND ISNUMERIC(ID)=1 /*(所影响的行数为 2 行) (所影响的行数为 1 行)服务器: 消息 245,级别 16,状态 1,行 7 将 varchar 值 'A' 转换为数据类型为 int 的列时发生语法错误。
贴SQL2000语句 select a.column1,a.column2,cast(a.column3 as float) from (select column1,column2,column3 from tb where isnumeric(column3)=1 and column2=5) a
select a.column1,a.column2,cast(a.column3 as float) from (select column1, column2, column3 from tb where isnumeric(column3)=1 and column2=5 ) a where isnumeric(column3) = 1
DECLARE @TB TABLE(ID VARCHAR(10),IDD INT) INSERT @TB SELECT 'A',1 UNION ALL SELECT 2,2 UNION ALL SELECT 2.0,3 select cast(a.ID as float) from (select * from @tb where isnumeric(ID)=1 AND IDD=2 ) a (所影响的行数为 3 行) ----------------------------------------------------- 2.0(所影响的行数为 1 行)没错啊,我晕
测试你写的SQL是没问题,但是我那查询还是错误!
select a.column1,a.column2,a.column3 from (select column1,column2,column3 from tb where isnumeric(column3)=1 and column2=5) a WHERE ISNUMERIC(a.column3 )=0你这样查还有数据吗
select a.column1,a.column2,cast(a.column3 as float) from (select column1, column2, column3 from tb where isnumeric(column3)=1
SELECT * FROM TB WHERE ISNUMERI(COL)=1
然后子查询再查,如果后面加条件应该也可以,不过不能加在前面
INSERT @TB SELECT 'A' UNION ALL SELECT 2
SELECT * FROM @TB WHERE ISNUMERIC(ID)=1 AND ID=2
/*(所影响的行数为 2 行)ID
----------
2(所影响的行数为 1 行)*/
SELECT * FROM @TB WHERE ID=2 AND ISNUMERIC(ID)=1
/*(所影响的行数为 2 行)
(所影响的行数为 1 行)服务器: 消息 245,级别 16,状态 1,行 7
将 varchar 值 'A' 转换为数据类型为 int 的列时发生语法错误。
from (select column1,column2,column3 from tb where isnumeric(column3)=1 and
column2=5) a
select a.column1,a.column2,cast(a.column3 as float)
from (select column1, column2, column3
from tb
where isnumeric(column3)=1
and column2=5
) a
where isnumeric(column3) = 1
INSERT @TB SELECT 'A',1 UNION ALL SELECT 2,2 UNION ALL SELECT 2.0,3
select cast(a.ID as float)
from (select * from @tb where isnumeric(ID)=1 AND IDD=2
) a
(所影响的行数为 3 行)
-----------------------------------------------------
2.0(所影响的行数为 1 行)没错啊,我晕
测试你写的SQL是没问题,但是我那查询还是错误!
from (select column1,column2,column3 from tb where isnumeric(column3)=1 and
column2=5) a WHERE ISNUMERIC(a.column3 )=0你这样查还有数据吗
from (select column1, column2, column3
from tb
where isnumeric(column3)=1
) a
where column2=5楼主这样吧,这样应该可以了,