if OBJECT_ID('t','U') is not null drop table t
go
create table t
(
    id nvarchar
)
go
insert into t
select 'a' union all
select '1'
go
select * from (
select * from t where ISNUMERIC(id)=1
)a 
/*
id
----
1
*/
go
select * from (
select * from t where ISNUMERIC(id)=1
)a where id>1
/*
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'a' to data type int.
*/

if OBJECT_ID('t','U') is not null drop table t
go
create table t
(
    id nvarchar
)
go
insert into t
select 'a' union all
select '1'
go
select * from (
select * from t where ISNUMERIC(id)=1
)a 
/*
id
----
1
*/
go
select * from (
select * from t where ISNUMERIC(id)=1
)a where ISNUMERIC(id)>=1
/*
id
1
*/

------解决方案--------------------

以前海爷讨论过这个。温习一下。

------解决方案--------------------

SQL code

if OBJECT_ID('t','U') is not null drop table t
go
create table t
(
    id nvarchar
)
go
insert into t
select 'a' union all
select '1' union all
select '5' union all
select '8'
go

select * into #t from t where ISNUMERIC(id)=1
select * from #t where id>4
/*
id
----
5
8
*/
drop table #t

--插入临时表是可以的，但是用with表达式就不行...

------解决方案--------------------

SQL code
use CSDN
go

if OBJECT_ID('t','U') is not null drop table t
go

create table t
(
    id nvarchar(100)
)
go

insert into t
select '$1' union all  --注意isnumeric函数的弊端
select '1' union all
select 'a'
go

--参考如下:
select right(replicate('0', 10)+id, 10) from t  --int类型不会超过10位
where patindex('%[^0-9]%', id) = 0
    and id > replicate('0', 9) + '1'

------解决方案--------------------
微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了

------解决方案--------------------
探讨

微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了


------解决方案--------------------
探讨


微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了