DECLARE @t TABLE(id INT ,Desp NVARCHAR(50)) INSERT @t SELECT 1,N'直管Φ10.1 红粗胚' UNION ALL SELECT 2,N'直管Φ10.5 黄粗胚' UNION ALL SELECT 3,N'直管Φ11.1 蓝粗胚' UNION ALL SELECT 4,N'直管Φ11.511 绿粗胚' UNION ALL SELECT 5,N'直管Φ12.1 橙粗胚' UNION ALL SELECT 6,N'直管Φ12.2 黑粗胚' UNION ALL SELECT 7,N'直管Φ12.3 白粗胚' UNION ALL SELECT 8,N'直管Φ12.4 青铜粗胚' UNION ALL SELECT 9,N'直管Φ12.5 铁粗胚' UNION ALL SELECT 10,N'xxx管Φ12.5 铁粗胚' UNION ALL SELECT 11,N'xxxyy管11.25铁粗胚' UNION ALL SELECT 12,N'zzyy管dd11.5铁粗12胚' /************/ /*Test Data*/ /***fcuandy**/ /*2007-12-02*/ /************/SELECT * FROM @t WHERE CEILING( STUFF( STUFF(Desp,1,PATINDEX(N'%[0-9.]%',Desp)-1,''), PATINDEX('%[^0-9.]%',STUFF(Desp,1,PATINDEX(N'%[0-9.]%',Desp)-1,'')) , 1000,'' ) )=12 /* 3 直管Φ11.1 蓝粗胚 4 直管Φ11.511 绿粗胚 11 xxxyy管11.25铁粗胚 12 zzyy管11.5铁粗12胚 */
where cast(substring(desp,4,4) as decimal(18,2)) between 11 and 12
where cast(stuff(desp,1,4,'') as decimal(18,2)) between 11 and 12
select * from tb
where cast(substring(desp,4,4) as decimal(18,2)) between 11 and 123 直管Φ11.1蓝粗胚
4 直管Φ11.5绿粗胚
思路就是先找到 Φ 的位置,再找 Φ 后面的空格,截取这之间的字符,然后转换为数字,再比较
insert into @tb select 1,'直管Φ10.1红粗胚'
insert into @tb select 1,'直管aaΦ10.2红粗胚'
insert into @tb select 1,'直管bbbΦ10.3红粗胚'
insert into @tb select 1,'直管ccccΦ10.4红粗胚'
insert into @tb select 1,'直管dddddΦ10.5红粗胚'select *,substring(desp,charindex('Φ',desp)+1,len(desp)-charindex('Φ',desp)-patindex('%[0-9]%',reverse(desp))+1)from @tbid desp (无列名)
1 直管Φ10.1红粗胚 10.1
1 直管aaΦ10.2红粗胚 10.2
1 直管bbbΦ10.3红粗胚 10.3
1 直管ccccΦ10.4红粗胚 10.4
1 直管dddddΦ10.5红粗胚 10.5最后一列
insert into tb values(1, '直管Φ10.1 红粗胚')
insert into tb values(2, '直管Φ10.5 黄粗胚')
insert into tb values(3, '直管Φ11.1 蓝粗胚')
insert into tb values(4, '直管Φ11.5 绿粗胚')
insert into tb values(5, '直管Φ12.1 橙粗胚')
insert into tb values(6, '直管Φ12.2 黑粗胚')
insert into tb values(7, '直管Φ12.3 白粗胚')
insert into tb values(8, '直管Φ12.4 青铜粗胚')
insert into tb values(9, '直管Φ12.5 铁粗胚')
insert into tb values(10, '直管Φ11.9 黑粗胚')
goselect * from tb where substring(desp , charindex('Φ' , desp) + 1 , 4) between '11' and '12'drop table tb/*
ID Desp
----------- --------------------------------------------------
3 直管Φ11.1 蓝粗胚
4 直管Φ11.5 绿粗胚
10 直管Φ11.9 黑粗胚(所影响的行数为 3 行)*/
insert into tb values(1, '直管Φ10.1 红粗胚')
insert into tb values(2, '直管Φ10.5 黄粗胚')
insert into tb values(3, '直管Φ11.1 蓝粗胚')
insert into tb values(4, '直管Φ11.5 绿粗胚')
insert into tb values(5, '直管Φ12.1 橙粗胚')
insert into tb values(6, '直管Φ12.2 黑粗胚')
insert into tb values(7, '直管Φ12.3 白粗胚')
insert into tb values(8, '直管Φ12.4 青铜粗胚')
insert into tb values(9, '直管Φ12.5 铁粗胚')
insert into tb values(10, '直管Φ11.9 黑粗胚')
go
--固定字符号串
select * from tb where substring(desp , charindex('Φ' , desp) + 1 , 4) between '11' and '12'
/*
ID Desp
----------- --------------------------------------------------
3 直管Φ11.1 蓝粗胚
4 直管Φ11.5 绿粗胚
10 直管Φ11.9 黑粗胚(所影响的行数为 3 行)
*/--不固定字符串
select * from tb where substring(desp , charindex('Φ' , desp) + 1 , charindex(' ',desp) - charindex('Φ' , desp) - 1 ) between '11' and '12'
/*
ID Desp
----------- --------------------------------------------------
3 直管Φ11.1 蓝粗胚
4 直管Φ11.5 绿粗胚
10 直管Φ11.9 黑粗胚
(所影响的行数为 3 行)
*/drop table tb
if object_id('tempdb..#')is not null drop table #
go
create table #(ID int, Desp nvarchar(20))
insert # select 1, N'直管Φ10.1 红粗胚'
insert # select 2, N'直管Φ10.5 黄粗胚'
insert # select 3, N'直管Φ11.1 蓝粗胚'
insert # select 4, N'直管Φ11.5 绿粗胚'
insert # select 5, N'直管Φ12.1 橙粗胚'
insert # select 6, N'直管Φ12.2 黑粗胚'
insert # select 7, N'直管Φ12.3 白粗胚'
insert # select 8, N'直管Φ12.4 青铜粗胚'
insert # select 9, N'直管Φ12.5 铁粗胚'
SELECT * FROM # WHERE CAST(SUBSTRING(DESP,CHARINDEX(N'Φ',DESP)+1,CHARINDEX(' ',DESP)-CHARINDEX(N'Φ',DESP)) AS FLOAT)>12
/*ID Desp
----------- --------------------
5 直管Φ12.1 橙粗胚
6 直管Φ12.2 黑粗胚
7 直管Φ12.3 白粗胚
8 直管Φ12.4 青铜粗胚
9 直管Φ12.5 铁粗胚*/
if object_id('tempdb..#')is not null drop table #
go
create table #(ID int, Desp nvarchar(20))
insert # select 1, N'直管Φ10.1 红粗胚'
insert # select 2, N'直管Φ10.5 黄粗胚'
insert # select 3, N'直管Φ11.1 蓝粗胚'
insert # select 4, N'直管Φ11.5 绿粗胚'
insert # select 5, N'直管Φ12.1 橙粗胚'
insert # select 6, N'直管Φ12.2 黑粗胚'
insert # select 7, N'直管Φ12.3 白粗胚'
insert # select 8, N'直管Φ12.4 青铜粗胚'
insert # select 9, N'直管Φ12.5 铁粗胚'
SELECT * FROM # WHERE CAST(SUBSTRING(DESP,CHARINDEX(N'Φ',DESP)+1,CHARINDEX(' ',DESP)-CHARINDEX(N'Φ',DESP)) AS FLOAT) BETWEEN 11.00 AND 12.00
/*ID Desp
----------- --------------------
3 直管Φ11.1 蓝粗胚
4 直管Φ11.5 绿粗胚
*/
如有些时候Φ 可能会写成"直径"这两个汉字,
数字后可能也会没有空格。能不能不往数字上面靠?
假如我可能要查询的是一个真正的某段范围的字符串,
比如要查DESP中含有“AB”至“DB”之间的所有字符串的字段呢?
--楼主看看这个函数的用法
2 直管Φ10 红粗胚.1
3 直Φ管10 红粗胚.1
4 1直Φ管0 红粗胚.1
5 直管10.1Φ 红粗胚
6 zhijin10.1Φ 红粗胚
...
INSERT @t SELECT 1,N'直管Φ10.1 红粗胚'
UNION ALL SELECT 2,N'直管Φ10.5 黄粗胚'
UNION ALL SELECT 3,N'直管Φ11.1 蓝粗胚'
UNION ALL SELECT 4,N'直管Φ11.511 绿粗胚'
UNION ALL SELECT 5,N'直管Φ12.1 橙粗胚'
UNION ALL SELECT 6,N'直管Φ12.2 黑粗胚'
UNION ALL SELECT 7,N'直管Φ12.3 白粗胚'
UNION ALL SELECT 8,N'直管Φ12.4 青铜粗胚'
UNION ALL SELECT 9,N'直管Φ12.5 铁粗胚'
UNION ALL SELECT 10,N'xxx管Φ12.5 铁粗胚'
UNION ALL SELECT 11,N'xxxyy管11.25铁粗胚'
UNION ALL SELECT 12,N'zzyy管dd11.5铁粗12胚'
/************/
/*Test Data*/
/***fcuandy**/
/*2007-12-02*/
/************/SELECT * FROM @t
WHERE CEILING(
STUFF(
STUFF(Desp,1,PATINDEX(N'%[0-9.]%',Desp)-1,''),
PATINDEX('%[^0-9.]%',STUFF(Desp,1,PATINDEX(N'%[0-9.]%',Desp)-1,''))
,
1000,''
)
)=12
/*
3 直管Φ11.1 蓝粗胚
4 直管Φ11.511 绿粗胚
11 xxxyy管11.25铁粗胚
12 zzyy管11.5铁粗12胚
*/