使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
我不相信SQL Server有内置的分裂函数,所以除了UDF,我知道的唯一其他答案是劫持PARSENAME函数:
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME接受一个字符串,并根据句点分隔它。它接受一个数字作为第二个参数,该数字指定要返回字符串的哪一部分(从后到前工作)。
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello
明显的问题是当字符串已经包含句点时。我仍然认为使用UDF是最好的方式……还有其他建议吗?
其他回答
I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].
DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT
JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
JSON_VALUE(@SampleString, '$[2]') AS Element3Value;
输出
Element1Value Element2Value Element3Value
--------------------- ------------------- ------------------------------
Hello John Smith
(1 row affected)
以下是我的解决方案,可能会对某些人有所帮助。修改以上Jonesinator的回答。
如果我有一个带分隔符的INT值字符串,并希望返回一个INT表(然后我可以加入)。如。44岁的1,3343 6,8765年
创建一个UDF:
IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO
CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @table TABLE
(
Value INT NOT NULL
)
AS
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)
WHILE LEN(@String) > 0
BEGIN
IF PATINDEX(@Pattern, @String) > 0
BEGIN
SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
INSERT INTO @table (Value) VALUES (@Value)
SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
END
ELSE
BEGIN
-- Just the one value.
INSERT INTO @table (Value) VALUES (@String)
RETURN
END
END
RETURN
END
GO
然后得到表格结果:
SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')
1
20
3
343
44
6
8765
在join语句中:
SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]
1 Elvis
20 Karen
3 David
343 Simon
44 Raj
6 Mike
8765 Richard
如果你想返回一个nvarchar列表而不是int,那么只需更改表定义:
RETURNS @table TABLE
(
Value NVARCHAR(MAX) NOT NULL
)
如果你查看下面关于使用SQL分割字符串的SQL教程,你会发现许多函数可以用于在SQL Server上分割给定的字符串
例如,SplitAndReturnNth UDF函数可用于使用分隔符分割文本,并将第n块作为函数的输出返回
select dbo.SplitAndReturnNth('Hello John Smith',' ',2)
Alter Function dbo.fn_Split
(
@Expression nvarchar(max),
@Delimiter nvarchar(20) = ',',
@Qualifier char(1) = Null
)
RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
AS
BEGIN
/* USAGE
Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
*/
-- Declare Variables
DECLARE
@X xml,
@Temp nvarchar(max),
@Temp2 nvarchar(max),
@Start int,
@End int
-- HTML Encode @Expression
Select @Expression = (Select @Expression For XML Path(''))
-- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
BEGIN
Select
-- Starting character position of @Qualifier
@Start = PATINDEX('%' + @Qualifier + '%', @Expression),
-- @Expression starting at the @Start position
@Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
-- Next position of @Qualifier within @Expression
@End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
-- The part of Expression found between the @Qualifiers
@Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
-- New @Expression
@Expression = REPLACE(@Expression,
@Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
Replace(@Temp2, @Delimiter, '|||***|||')
)
END
-- Replace all occurences of @Delimiter within @Expression with '</fn_Split><fn_Split>'
-- And convert it to XML so we can select from it
SET
@X = Cast('<fn_Split>' +
Replace(@Expression, @Delimiter, '</fn_Split><fn_Split>') +
'</fn_Split>' as xml)
-- Insert into our returnable table replacing '|||***|||' back to @Delimiter
INSERT @Results
SELECT
"Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
FROM
@X.nodes('fn_Split') as X(C)
-- Return our temp table
RETURN
END
如果数据库的兼容性级别为130或更高,则可以使用STRING_SPLIT函数和OFFSET FETCH子句按索引获取特定的项。
要获得索引N(从零开始)的项,可以使用以下代码
SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY
要检查数据库的兼容性级别,执行以下代码:
SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDBName';