declare @s as varchar(50) set @s = '123/543/aaaa/444444' declare @m as varchar(10) set @m = 'aaaa'select case when n1 = @m then 1 when n2 = @m then 2 when n3 = @m then 3 when n4 = @m then 4 end from ( select parsename(replace(@s,'/','.'),4) n1, parsename(replace(@s,'/','.'),3) n2, parsename(replace(@s,'/','.'),2) n3, parsename(replace(@s,'/','.'),1) n4 ) t/*
----------- 3(所影响的行数为 1 行) */
declare @s varchar(100) set @s='123/543/aaaa/444444' select * from ( select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s, pos=number-LEN(REPLACE(left(@s,number),'/',''))+1 from master..spt_values where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l where pos=3 s pos ---------------------------------------------------------------------------------------------------- ----------- aaaa 3
declare @s varchar(100) set @s='123/543/aaaa/444444' select * from ( select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s, pos=ROW_NUMBER() over(Order by getdate()) from master..spt_values where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l where pos=3 0r
declare @s varchar(100) set @s='123/543/aaaa/444444' select pos from ( select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s, pos=ROW_NUMBER() over(Order by getdate()) from master..spt_values where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l where s='aaaa' ------------------------- declare @s varchar(100) set @s='123/543/aaaa/444444' select pos from ( select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s, pos=number-LEN(REPLACE(left(@s,number),'/',''))+1 from master..spt_values where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l where s='aaaa'pos ----------- 3
DECLARE @STR VARCHAR(500) SET @STR='123/543/aaaa/444444/aaa/333/rrrrrr' DECLARE @SQL VARCHAR(5000) SET @SQL='SELECT COL= '''+REPLACE(@STR,'/',''' UNION ALL '+CHAR(10)+CHAR(13)+'SELECT ''')+'''' --PRINT @SQL --DROP TABLE TBTEST --CREATE TABLE TBTEST (COL NVARCHAR(10),ID INT IDENTITY(1,1)) INSERT TBTEST(COL) EXEC(@SQL)SELECT * FROM TBTEST WHERE COL='AAAA'COL ID ---------- ----------- aaaa 3(所影响的行数为 1 行)
再用PARSENAME
/
--------------------------------------------------------------------------------------------------------------------------------
aaaa(所影响的行数为 1 行)
set @s = '123/543/aaaa/444444'
declare @m as varchar(10)
set @m = 'aaaa'select case when n1 = @m then 1
when n2 = @m then 2
when n3 = @m then 3
when n4 = @m then 4
end
from
(
select parsename(replace(@s,'/','.'),4) n1,
parsename(replace(@s,'/','.'),3) n2,
parsename(replace(@s,'/','.'),2) n3,
parsename(replace(@s,'/','.'),1) n4
) t/*
-----------
3(所影响的行数为 1 行)
*/
set @s='123/543/aaaa/444444'
select * from (
select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s,
pos=number-LEN(REPLACE(left(@s,number),'/',''))+1
from master..spt_values
where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l
where pos=3
s pos
---------------------------------------------------------------------------------------------------- -----------
aaaa 3
set @s='123/543/aaaa/444444'
select * from (
select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s,
pos=ROW_NUMBER() over(Order by getdate())
from master..spt_values
where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l
where pos=3
0r
2:我要取的子字符串也是不确定的.例如说我现在要取333是排在第几个分隔符后...
where pos=3--这里的POS是第几个 自己可以写。。
换下WHERE的条件 晕。。
我不是要知道第N位是什么子字符串,而是想通过子字符串而得出在第N位.
set @s='123/543/aaaa/444444'
select pos from (
select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s,
pos=ROW_NUMBER() over(Order by getdate())
from master..spt_values
where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l
where s='aaaa'
-------------------------
declare @s varchar(100)
set @s='123/543/aaaa/444444'
select pos from (
select SUBSTRING(@s,number,CHARINDEX('/',@s+'/',number)-number) as s,
pos=number-LEN(REPLACE(left(@s,number),'/',''))+1
from master..spt_values
where number<LEN(@s) and type='p' and SUBSTRING('/'+@s,number,1)='/') l
where s='aaaa'pos
-----------
3
SET @STR='123/543/aaaa/444444/aaa/333/rrrrrr'
DECLARE @SQL VARCHAR(5000)
SET @SQL='SELECT COL= '''+REPLACE(@STR,'/',''' UNION ALL '+CHAR(10)+CHAR(13)+'SELECT ''')+''''
--PRINT @SQL
--DROP TABLE TBTEST
--CREATE TABLE TBTEST (COL NVARCHAR(10),ID INT IDENTITY(1,1))
INSERT TBTEST(COL) EXEC(@SQL)SELECT * FROM TBTEST WHERE COL='AAAA'COL ID
---------- -----------
aaaa 3(所影响的行数为 1 行)
不好意思啦,我不是愚弄大家,只是想看有没有更巧妙的办法嘛...我觉得你的这个办法就不错,只是与我要的结果背道而驰....
SELECT PARSENAME(REPLACE('123/543/aaaa/444444','/','.'),2)
declare @s nvarchar(3000)
declare @i int,@ii int
select @i= 1,@ii = 1
select @s = '123/543/aaaa/444444'
select @s = substring(@s,1,charindex('aaaa',@s,1)-1)
while charindex('/',@s,@ii) > 0
begin
set @ii = charindex('/',@s,@ii) + 1
set @i = @i + 1
end
select @i as 位置