一个比较难的查询问题,是高手的来看一下.

[kevinworkroom]

floor
------------------------
5,5
0,6
11,10
0,1
0,8
0,3
0,11
0,10
0,1
0,9


上面是字段和数据
如何用sql查询出第二位数字是8-13的数据

用like '%,[8-9]' or like '%,1[0-3]'是可以查询出来,但我想只用一个件就能把数据查询出来.
明说一下[8-13]这样是没有办法用的.

[昵称被占用了]

select * from tab
where cast(stuff(floor,1,charindex(',',floor),'') as int) between 8 and 13

[昵称被占用了]

测试

declare @tab table(
[floor] varchar(10)
)

insert @tab select
'5,5'
union all select 
'0,6' 
union all select 
'11,10' 
union all select 
'0,1' 
union all select 
'0,8' 
union all select 
'0,3' 
union all select 
'0,11' 
union all select 
'0,10' 
union all select 
'0,1' 
union all select 
'0,9' 

select * from @tab
where cast(stuff([floor],1,charindex(',',[floor]),'') as int) between 8 and 13

--结果
floor
----------
11,10
0,8
0,11
0,10
0,9

(5 行受影响)

[xieyueqing]

select [floor] from (
select [floor],cast(right([floor],len([floor])- charindex(',',[floor])) as int) n from table1 ) T
where n between 8 and 13

[flairsky]

create table #t (num varchar(10))
insert into #t
select '5,5'
union all
select '0,6'
union all
select '11,10'
union all
select '0,1'
union all
select '0,8'
union all
select '0,3'
union all
select '0,11'
union all
select '0,10'
union all
select '0,1'
union all
select '0,9'

select * from #t
where convert(tinyint,substring(num,charindex(',',num)+1,2)) between 8 and 13

drop table #t

[昵称被占用了]

declare @tab table(
[floor] varchar(10)
)

insert @tab select
'5,5'
union all select 
'0,6' 
union all select 
'11,10' 
union all select 
'0,1' 
union all select 
'0,8' 
union all select 
'0,3' 
union all select 
'0,11' 
union all select 
'0,10' 
union all select 
'0,1' 
union all select 
'0,9' 

select * from @tab
where PARSENAME(REPLACE ([floor],',','.'),1) between 8 and 13

--结果
floor
----------
11,10
0,8
0,11
0,10
0,9

(5 行受影响)

[fgh63]

就是切“,”后面的字符串转整再过滤~~

[fgh63]

感觉写着更麻烦，为什么不要两个条件，非要1个呢？

[chuifengde]

DECLARE @a table(floor varchar(20))
------------------------ 
INSERT @a SELECT '5,5' 
UNION ALL SELECT '0,6' 
UNION ALL SELECT '11,10' 
UNION ALL SELECT '0,1' 
UNION ALL SELECT '0,8'
UNION ALL SELECT '0,3' 
UNION ALL SELECT '0,11' 
UNION ALL SELECT '0,10' 
UNION ALL SELECT '0,1' 
UNION ALL SELECT '0,9' 

SELECT * FROM @a WHERE right('0'+parsename(replace(floor,',','.'),1),2) BETWEEN '08' AND '13'

/*floor                
-------------------- 
11,10
0,8
0,11
0,10
0,9

（所影响的行数为 5 行）*/

[kevinworkroom]

很久没有来了,没有想到现在这里高手这么多,学习了

[$扫地僧$]

create table T
(  
   T_Str varchar(8000)
)


insert T select '5,5' 
insert T select '0,6' 
insert T select '11,10' 
insert T select '0,1' 
insert T select '0,8' 
insert T select '0,3' 
insert T select '0,11' 
insert T select '0,10' 
insert T select '0,1' 
insert T select '0,9'

select *
from T
where charindex(',',T_Str)>0 
  and cast(SUBSTRING(T_Str,charindex(',',T_Str)+1,len(T_Str)-charindex(',',T_Str)) as int) between 8 and 13