基于分隔符的 T-SQL 拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21768321/
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
T-SQL split string based on delimiter
提问by Sesame
I have some data that I would like to split based on a delimiter that may or may not exist.
我有一些数据想要根据可能存在也可能不存在的分隔符进行拆分。
Example data:
示例数据：
John/Smith
Jane/Doe
Steve
Bob/Johnson
I am using the following code to split this data into First and Last names:
我使用以下代码将此数据拆分为名字和姓氏：
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable
The results I would like:
我想要的结果：
FirstName---LastName
John--------Smith
Jane--------Doe
Steve-------NULL
Bob---------Johnson
This code works just fine as long as all the rows have the anticipated delimiter, but errors out when a row does not:
只要所有行都具有预期的分隔符，此代码就可以正常工作，但是当行没有时会出错：
"Invalid length parameter passed to the LEFT or SUBSTRING function."
How can a re-write this to work properly?
如何重新编写它才能正常工作？
回答by sureshhh
May be this will help you.
可能这会帮助你。
SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
WHEN 0
THEN LEN(myColumn)
ELSE CHARINDEX('/', myColumn) - 1
END) AS FirstName
,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
WHEN 0
THEN LEN(myColumn) + 1
ELSE CHARINDEX('/', myColumn) + 1
END, 1000) AS LastName
FROM MyTable
回答by raga
For those looking for answers for SQL Server 2016+. Use the built-in STRING_SPLIT function
对于那些正在寻找 SQL Server 2016+ 答案的人。使用内置的 STRING_SPLIT 函数
Eg:
例如：
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
Reference: https://msdn.microsoft.com/en-nz/library/mt684588.aspx
回答by chaitanya.moguluri
SELECT CASE
WHEN CHARINDEX('/', myColumn, 0) = 0
THEN myColumn
ELSE LEFT(myColumn, CHARINDEX('/', myColumn, 0)-1)
END AS FirstName
,CASE
WHEN CHARINDEX('/', myColumn, 0) = 0
THEN ''
ELSE RIGHT(myColumn, CHARINDEX('/', REVERSE(myColumn), 0)-1)
END AS LastName
FROM MyTable
回答by jpw
Try filtering out the rows that contain strings with the delimiter and work on those only like:
尝试使用分隔符过滤掉包含字符串的行，然后只处理如下内容：
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable
WHERE CHARINDEX('/', myColumn) > 0
Or
或者
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable
WHERE myColumn LIKE '%/%'
回答by Michael Kwon
I just wanted to give an alternative way to split a string with multiple delimiters, in case you are using a SQL Server version under 2016.
我只是想提供一种使用多个分隔符拆分字符串的替代方法，以防您使用 2016 年以下的 SQL Server 版本。
The general idea is to split out all of the characters in the string, determine the position of the delimiters, then obtain substrings relative to the delimiters. Here is a sample:
大体思路是将字符串中的所有字符拆分出来，确定定界符的位置，然后得到相对于定界符的子串。这是一个示例：
-- Sample data
DECLARE @testTable TABLE (
TestString VARCHAR(50)
)
INSERT INTO @testTable VALUES
('Teststring,1,2,3')
,('Test')
DECLARE @delimiter VARCHAR(1) = ','
-- Generate numbers with which we can enumerate
;WITH Numbers AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N < 255
),
-- Enumerate letters in the string and select only the delimiters
Letters AS (
SELECT n.N
, SUBSTRING(t.TestString, n.N, 1) AS Letter
, t.TestString
, ROW_NUMBER() OVER ( PARTITION BY t.TestString
ORDER BY n.N
) AS Delimiter_Number
FROM Numbers n
INNER JOIN @testTable t
ON n <= LEN(t.TestString)
WHERE SUBSTRING(t.TestString, n, 1) = @delimiter
UNION
-- Include 0th position to "delimit" the start of the string
SELECT 0
, NULL
, t.TestString
, 0
FROM @testTable t
)
-- Obtain substrings based on delimiter positions
SELECT t.TestString
, ds.Delimiter_Number + 1 AS Position
, SUBSTRING(t.TestString, ds.N + 1, ISNULL(de.N, LEN(t.TestString) + 1) - ds.N - 1) AS Delimited_Substring
FROM @testTable t
LEFT JOIN Letters ds
ON t.TestString = ds.TestString
LEFT JOIN Letters de
ON t.TestString = de.TestString
AND ds.Delimiter_Number + 1 = de.Delimiter_Number
OPTION (MAXRECURSION 0)
回答by James Moore
The examples above work fine when there is only one delimiter, but it doesn't scale well for multiple delimiters. Note that this will only work for SQL Server 2016 and above.
当只有一个分隔符时，上面的示例工作正常，但对于多个分隔符，它不能很好地扩展。请注意，这仅适用于 SQL Server 2016 及更高版本。
/*Some Sample Data*/
DECLARE @mytable TABLE ([id] VARCHAR(10), [name] VARCHAR(1000));
INSERT INTO @mytable
VALUES ('1','John/Smith'),('2','Jane/Doe'), ('3','Steve'), ('4','Bob/Johnson')
/*Split based on delimeter*/
SELECT P.id, [1] 'FirstName', [2] 'LastName', [3] 'Col3', [4] 'Col4'
FROM(
SELECT A.id, X1.VALUE, ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY A.id) RN
FROM @mytable A
CROSS APPLY STRING_SPLIT(A.name, '/') X1
) A
PIVOT (MAX(A.[VALUE]) FOR A.RN IN ([1],[2],[3],[4],[5])) P
回答by Bruno
ALTER FUNCTION [dbo].[split_string](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
回答by codingWannabe
These all helped me get to this. I am still on 2012 but now have something quick that will allow me to split a string, even if string has varying numbers of delimiters, and grab the nth substring from that string. It's quick too. I know this post is old, but it took me forever to find something so hopefully this will help someone else.
这些都帮助我做到了这一点。我仍然在 2012 年，但现在有一些快速的东西可以让我分割一个字符串，即使字符串有不同数量的分隔符，并从该字符串中获取第 n 个子字符串。它也很快。我知道这篇文章很旧，但我花了很长时间才找到一些东西，所以希望这会对其他人有所帮助。
CREATE FUNCTION [dbo].[SplitsByIndex]
(@separator VARCHAR(20) = ' ',
@string VARCHAR(MAX),
@position INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @results TABLE
(id INT IDENTITY(1, 1),
chrs VARCHAR(8000)
);
DECLARE @outResult VARCHAR(8000);
WITH X(N)
AS (SELECT 'Table1'
FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)),
Y(N)
AS (SELECT 'Table2'
FROM X A1,
X A2,
X A3,
X A4,
X A5,
X A6,
X A7,
X A8), -- Up to 16^8 = 4 billion
T(N)
AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER(
ORDER BY
(
SELECT NULL
)) - 1 N
FROM Y),
Delim(Pos)
AS (SELECT t.N
FROM T
WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator
OR t.N = 0)),
Separated(value)
AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER(
ORDER BY
(
SELECT NULL
))-d.Pos - LEN(@separator))
FROM Delim d
WHERE @string IS NOT NULL)
INSERT INTO @results(chrs)
SELECT s.value
FROM Separated s
WHERE s.value <> @separator;
SELECT @outResult =
(
SELECT chrs
FROM @results
WHERE id = @position
);
RETURN @outResult;
END;
This can be used like this:
这可以像这样使用：
SELECT [dbo].[SplitsByIndex](' ',fieldname,2)
from tablename
