34,588
社区成员
发帖
与我相关
我的任务
分享
if not object_id('tb') is null
drop table tb
Go
Create table tb([CODE] nvarchar(5),[Description] nvarchar(28))
Insert tb
select N'MAT01',N'52%NYLON+8%SPANDEX+40%COTTON' union all
select N'MAT02',N'53%NL+7%SPANDEX+40%COTTON' union all
select N'MAT03',N'24%NYLON+6%SPANDEX+70%COTTON' union all
select N'MAT04',N'55%NYLON+5%SPANDEX+40%COTTON' union all
select N'MAT05',N'36%NL+4%SPANDEX+60%COTTON' union all
select N'MAT06',N'57%NYLON+3%SPANDEX+40%COTTON' union all
select N'MAT07',N'58%NL+2%SPANDEX+40%COTTON'
Go
/*
Select *
from tb
where
substring([Description],
charindex('+',[Description])+1,
charindex('%',[Description],charindex('%',[Description])+1)
-
charindex('+',[Description])
-1)
between 4 and 6
*/
select * from
(
Select *,
reverse( parsename( reverse(replace(Description,'+','.')) ,1) ) as f1, --把分隔符换成".", 取反, 取分隔的第n节,再取反回来
reverse( parsename( reverse(replace(Description,'+','.')) ,2) ) as f2,
reverse( parsename( reverse(replace(Description,'+','.')) ,3) ) as f3,
reverse( parsename( reverse(replace(Description,'+','.')) ,4) ) as f4,
reverse( parsename( reverse(replace(Description,'+','.')) ,5) ) as f5
from tb
) a
where substring(f2, 1, charindex('%',f2)-1) between 4 and 6
if not object_id('tb') is null
drop table tb
Go
Create table tb([CODE] nvarchar(5),[Description] nvarchar(28))
Insert tb
select N'MAT01',N'52%NYLON+8%SPANDEX+40%COTTON' union all
select N'MAT02',N'53%NL+7%SPANDEX+40%COTTON' union all
select N'MAT03',N'24%NYLON+6%SPANDEX+70%COTTON' union all
select N'MAT04',N'55%NYLON+5%SPANDEX+40%COTTON' union all
select N'MAT05',N'36%NL+4%SPANDEX+60%COTTON' union all
select N'MAT06',N'57%NYLON+3%SPANDEX+40%COTTON' union all
select N'MAT07',N'58%NL+2%SPANDEX+40%COTTON'
Go
Select *
from tb
where substring([Description],charindex('+',[Description])+1,charindex('%',[Description],charindex('%',[Description])+1)-charindex('+',[Description])-1)
between 4 and 6
/*
CODE Description
----- ----------------------------
MAT03 24%NYLON+6%SPANDEX+70%COTTON
MAT04 55%NYLON+5%SPANDEX+40%COTTON
MAT05 36%NL+4%SPANDEX+60%COTTON
(3 個資料列受到影響)
*/