--没有错啊!
create table test(username char(20))insert test
select 'test000001'
union all select 'test000002'
union all select 'test000003'
union all select 'test000004'
union all select 'test000005'
union all select 'test000006'
union all select 'test000010'
union all select 'test000020'
union all select 'test000023'select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int))>1) and ((CAST(RIGHT(rtrim(UserName),6)AS int))<6)username
--------------------
test000002
test000003
test000004
test000005 (所影响的行数为 4 行)
create table test(username char(20))insert test
select 'test000001'
union all select 'test000002'
union all select 'test000003'
union all select 'test000004'
union all select 'test000005'
union all select 'test000006'
union all select 'test000010'
union all select 'test000020'
union all select 'test000023'select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int))>1) and ((CAST(RIGHT(rtrim(UserName),6)AS int))<6)username
--------------------
test000002
test000003
test000004
test000005 (所影响的行数为 4 行)
where cast(right(rtrim(UserName),6) as int) between 1 and 10
将 varchar 值 '10.010' 转换为数据类型为 int 的列时发生语法错误。
我一运行查询1-10范围内的SQL就报错,如果,不是查询范围就不错服务器: 消息 245,级别 16,状态 1,行 3
将 varchar 值 '10.010' 转换为数据类型为 int 的列时发生语法错误。
----------------------------很明显,你的username的值有错,你的某条记录的username 值为'10.010'
中间出现了一个点
没有查询出数据
没有查询出数据。这个问题真是奇怪了。
select right(username,4) ,username
from test
order by right(username,4)
from test
WHERE isnumeric(RIGHT(rtrim(UserName),6))
and (RIGHT(rtrim(UserName),6) like '00000[1-9]'
or RIGHT(rtrim(UserName),6) like '000010')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
create table test(username char(20) null)goinsert into test
select 'test000001' union all
select 'test000002' union all
select 'test000003' union all
select 'test000004' union all
select 'test000005' union all
select 'test000006' union all
select 'test000010' union all
select 'test000020' union all
select 'test000023'
goselect * from test
/*
username
test000001
test000002
test000003
test000004
test000005
test000006
test000010
test000020
test000023 */
--------------------------A(s)---------------------------------------
select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int))>1)
/*
username
test000002
test000003
test000004
test000005
test000006
test000010
test000020
test000023 */
--------------------------A(e)-----------------------------------------------------------------B(s)---------------------------------------
--如果,要查询1-10范围内的数据就不知道怎么做了,用下面方法出错
--答復,結果可以出來,但結果不對
select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int))>1) and ((CAST(RIGHT(rtrim(UserName),6)AS int))<6)
/*
username
test000002
test000003
test000004
test000005 */
--上述語句修改為:
select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int))>=1) and ((CAST(RIGHT(rtrim(UserName),6)AS int))<=10)
/*
username
test000001
test000002
test000003
test000004
test000005
test000006
test000010
*/
--------------------------B(e)-----------------------------------------------------------------C(s)---------------------------------------
--原來語句為:select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and ((CAST(RIGHT(rtrim(UserName),6)AS int)) between 1 and 10,多了一個左括號'(',修正如下:
select * from test WHERE isnumeric(RIGHT(rtrim(UserName),6)) = 1 and (CAST(RIGHT(rtrim(UserName),6)AS int)) between 1 and 10
/*
username
test000001
test000002
test000003
test000004
test000005
test000006
test000010
*/
--------------------------C(e)---------------------------------------