查找字符串中分隔字符的位置 (SQL Server)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1924600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find position of delimited character in a String (SQL Server)
提问by Faizan Dosani
I have a string Variable
我有一个字符串变量
test=HARIA|123|MALE|STUDENT|HOUSEWIFE
test=HARIA|123|男性|学生|家庭主妇
i am using |as delimited character. if i want to extract data from 2nd occurence of pipe till 3rd occurrence. i need to get 'MALE' from above string.
我正在使用| 作为分隔符。如果我想从管道的第二次出现直到第三次出现提取数据。我需要从上面的字符串中获取“MALE”。
any help appreciated
任何帮助表示赞赏
回答by gbn
Untested
未经测试
SELECT
SUBSTRING (
@test,
CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1,
CHARINDEX('|', @test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1) - 1
)
A nicer way would be split the string into a table, and use ROW_NUMBER() to extract the 3rd element. Based on this Arrays and Lists in SQL Server
更好的方法是将字符串拆分为一个表，并使用 ROW_NUMBER() 提取第三个元素。基于此SQL Server 中的数组和列表
DECLARE @test varchar(100) = 'HARIA|123|MALE|STUDENT|HOUSEWIFE'
SELECT TOP 8000
Num
INTO
#Number
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY c1.object_id) AS Num
FROM
sys.columns c1, sys.columns c2, sys.columns c3
) N
SELECT
ROW_NUMBER() OVER (ORDER BY Num) AS Rank,
LTRIM(RTRIM(SUBSTRING(@test,
Num,
CHARINDEX('|', @test + '|', Num) - Num
))) AS Value
FROM
#Number
WHERE
Num <= LEN (@test)
AND
SUBSTRING('|' + @test, Num, 1) = '|'
DROP TABLE #Number
回答by priyanka.sarkar
Try this
尝试这个
Solution 1:(Using a number table)
解决方案1：（使用数字表）
declare @str varchar(1000)
set @str ='HARIA|123|MALE|STUDENT|HOUSEWIFE'
--Creating a number table
;with numcte as(
select 1 as rn union all select rn+1 from numcte where rn<LEN(@str)),
--Get the position of the "|" charecters
GetDelimitedCharPos as(
select ROW_NUMBER() over(order by getdate()) seq, rn,delimitedcharpos
from numcte
cross apply(select SUBSTRING(@str,rn,1)delimitedcharpos) X where delimitedcharpos = '|')
--Applying the formula SUBSTRING(@str,startseq + 1,endseq-startseq + 1)
-- i.e. SUBSTRING(@str,11,15-11) in this case
select top 1 SUBSTRING(
@str
,(select top 1 rn+1 from GetDelimitedCharPos where seq =2)
,(select top 1 rn from GetDelimitedCharPos where seq =3) -
(select top 1 rn+1 from GetDelimitedCharPos where seq =2)
) DesiredResult
from GetDelimitedCharPos
Solution 2:(Using XQuery)
解决方案 2：（使用 XQuery）
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='HARIA|123|MALE|STUDENT|HOUSEWIFE'
SET @xml = cast(('<X>'+replace(@str,'|' ,'</X><X>')+'</X>') as xml)
SELECT ShrededData as DesiredResult FROM(
SELECT
ROW_NUMBER() over(order by getdate()) rn
,N.value('.', 'varchar(10)') as ShrededData FROM @xml.nodes('X') as T(N))X
WHERE X.rn = 3 -- Specifying the destination sequence value(here 3)
Output(in both the cases)
输出（在两种情况下）
DesiredResult
MALE
