autoid类性为nvarchar;
数据如下
c1
c2
c12
c12
c5
如果SQL语句写select * from bb where autoid between c1 and c12这样不能显示为全部的数据.怎么写才能显示全部信息.因为Autoid是nvarchar问题.怎么写才能避免!! 谢谢1
数据如下
c1
c2
c12
c12
c5
如果SQL语句写select * from bb where autoid between c1 and c12这样不能显示为全部的数据.怎么写才能显示全部信息.因为Autoid是nvarchar问题.怎么写才能避免!! 谢谢1
select * from bb where cast(right(autoid,len(autoid)-1) as int) between cast(right('c1',len('c1')-1) as int) and cast(right('c12',len('c12')-1) as int)
select * from bb where autoid between c1 and c12
--不要条件就是全部了啊!
select * from bb
*
from
bb
where
cast(right(autoid,len(autoid)-1) as int) between 1 and 12
if object_id('[bb]') is not null drop table [bb]
go
create table [bb]([autoid] varchar(3))
insert [bb]
select 'c1' union all
select 'c2' union all
select 'c12' union all
select 'c12' union all
select 'c5'
---查询---
select
*
from
bb
where
cast(right(autoid,len(autoid)-1) as int) between 1 and 12---结果---
autoid
------
c1
c2
c12
c12
c5(所影响的行数为 5 行)
h1
h2
h3
h5
h7
我就想查询c1-c12或h1-h7或直接c1-h7该怎么写?
if object_id('[bb]') is not null drop table [bb]
go
create table [bb]([autoid] varchar(3))
insert [bb]
select 'c1' union all
select 'c2' union all
select 'c12' union all
select 'c12' union all
select 'c5' union all
select 'h1' union all
select 'h2' union all
select 'h3' union all
select 'h5' union all
select 'h7'
---查询---
select
*
from bb
where
left([autoid],1)='h' and cast(right(autoid,len(autoid)-1) as int) between 1 and 7---结果---
/*
autoid
------
h1
h2
h3
h5
h7(5 行受影响)*/
select
*
from
bb
where
left(autoid,1)='h'
and
cast(right(autoid,len(autoid)-1) as int) between 1 and 7/**
autoid
------
h1
h2
h3
h5
h7
**/