nvarchar 和 int 比较解决方法_Sql Server

nvarchar 和 int 比较

SQL code

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

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

探讨
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……

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

SQL code

if OBJECT_ID('t','U') is not null drop table tgocreate table t(    id nvarchar)goinsert into tselect 'a' union allselect '1'goselect * from (select * from t where ISNUMERIC(id)=1)a /*id----1*/goselect * from (select * from t where ISNUMERIC(id)=1)a where ISNUMERIC(id)>=1/*id1*/
------解决方案--------------------
以前海爷讨论过这个。温习一下。
------解决方案--------------------
SQL codeif OBJECT_ID('t','U') is not null drop table tgocreate table t(    id nvarchar)goinsert into tselect 'a' union allselect '1' union allselect '5' union allselect '8'goselect * into #t from t where ISNUMERIC(id)=1select * from #t where id>4/*id----58*/drop table #t--插入临时表是可以的，但是用with表达式就不行...
------解决方案--------------------
SQL codeuse CSDNgoif OBJECT_ID('t','U') is not null drop table tgocreate table t(    id nvarchar(100))goinsert into tselect '$1' union all  --注意isnumeric函数的弊端select '1' union allselect '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'
------解决方案--------------------
微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了
------解决方案--------------------
探讨
微软想帮你优化引出的问题，原谅他吧，知道有这个问题就可以了

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

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