使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
解析姓和名的简单解决方案
DECLARE @Name varchar(10) = 'John Smith'
-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))
-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))
在我的例子中(在许多其他人中似乎也是如此……),我有一个由一个空格隔开的姓和名列表。可以直接在选择语句中使用它来解析姓和名。
-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable
其他回答
通过delimeter函数得到字符串的n个部分:
create function GetStringPartByDelimeter (
@value as nvarchar(max),
@delimeter as nvarchar(max),
@position as int
) returns NVARCHAR(MAX)
AS BEGIN
declare @startPos as int
declare @endPos as int
set @endPos = -1
while (@position > 0 and @endPos != 0) begin
set @startPos = @endPos + 1
set @endPos = charindex(@delimeter, @value, @startPos)
if(@position = 1) begin
if(@endPos = 0)
set @endPos = len(@value) + 1
return substring(@value, @startPos, @endPos - @startPos)
end
set @position = @position - 1
end
return null
end
以及用法:
select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)
返回:
c
下面是一个函数,它将完成问题的目标,即分割字符串并访问项目X:
CREATE FUNCTION [dbo].[SplitString]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255),
@ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @inp VARCHAR(MAX)
SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))
DECLARE @xml XML
SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'
DECLARE @ret VARCHAR(MAX)
SET @ret = (SELECT
el = split.el.value('.','varchar(max)')
FROM @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))
RETURN @ret
END
用法:
SELECT dbo.SplitString('Hello John Smith', ' ', 2)
结果:
John
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
可以利用Number表进行字符串解析。
创建一个物理数字表:
create table dbo.Numbers (N int primary key);
insert into dbo.Numbers
select top 1000 row_number() over(order by number) from master..spt_values
go
创建具有1000000行的测试表
create table #yak (i int identity(1,1) primary key, array varchar(50))
insert into #yak(array)
select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
go
创建函数
create function [dbo].[ufn_ParseArray]
( @Input nvarchar(4000),
@Delimiter char(1) = ',',
@BaseIdent int
)
returns table as
return
( select row_number() over (order by n asc) + (@BaseIdent - 1) [i],
substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
from dbo.Numbers
where n <= convert(int, len(@Input)) and
substring(@Delimiter + @Input, n, 1) = @Delimiter
)
go
使用情况(在我的笔记本电脑上40秒内输出3mil行)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, ',', 1)
清理
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
这里的性能并不惊人,但在100万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。
这里有一个UDF可以做到这一点。它将返回一个带分隔符的值的表,我还没有尝试所有的场景,但您的示例工作良好。
CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
GO
你可以这样称呼它:
Select * From SplitString('Hello John Smith',' ')
编辑:使用len>1处理分隔符的更新解决方案如下:
select * From SplitString('Hello**John**Smith','**')