nvarchar 和 int 比较

[bean_sql]

	
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.
*/

[--小F--]

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

[叶子]

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表达式就不行...

[Shawn]

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'

[昵称被占用了]

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

[SQL777]

[Quote=引用 8 楼 的回复:]
微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了
[/Quote]
嘿嘿。人总有放错的时候。何况是软件。海爷说的是

[黄_瓜]

[Quote=引用 8 楼 的回复:]

微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了
[/Quote]
对，他把语句优化为

select * into #t from t where ISNUMERIC(id)=1 and  id>4

同时筛选 而不是先里面后外面。

[發糞塗牆]

[Quote=引用楼主 的回复:]
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'
go
select * from (
select * from t where ISNUMERI……
[/Quote]
在最后的where id>1那里改成where ISNUMERIC(id)>1

[bean_sql]

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

[Felixzhaowenzhong]

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
*/

[bean_sql]

感谢你的答复，
解决方案如下：

	
if OBJECT_ID('t','U') is not null drop table t
go
create table t
(
	id nvarchar(10)
)
go
insert into t
select 'a' union all
select '10'
go
select * from (
select * from t where ISNUMERIC(id)=1
)a 
/*
id
----
10
*/
go
------------------------------------------------解决方案
select * from (
	select 
		case when ISNUMERIC(id)=1 
			 then CONVERT(numeric,id)
			 else CONVERT(numeric,0)
		end as id 
	from t where ISNUMERIC(id)=1
)a where id>1
/*
id
----
10
*/

[Quote=引用 2 楼 的回复:]

SQL code

if OBJECT_ID('t','U') is not null drop table t
go
create table t
(
id nvarchar(10)
)
go
insert into t
select 'a' union all
select '10'
go
select * from (
select * from t where ISNUMERI……
[/Quote]