Wie teile ich einen abgegrenzten String auf, um auf einzelne Elemente zugreifen zu können?

Question

Mit SQL Server, wie kann ich einen String aufteilen, um auf Element x zugreifen zu können?

Nehmen wir einen String "Hallo John Smith". Wie kann ich den String nach Leerzeichen aufteilen und auf das Element an Index 1 zugreifen, das "John" zurückgeben sollte?

Answer 1

Hier ist eine Funktion, die das Ziel der Frage erreichen wird, einen String zu zerlegen und das Element X zu erreichen:

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 = '' + REPLACE(@inp,'_DELMTR_','') + ''

       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

Verwendung:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

Ergebnis:

John

Answer 2

Reine setbasierte Lösung mit rekursiver CTE unter Verwendung von TVF. Sie können diese Funktion mit JOIN und APPLY auf jedes Dataset anwenden.

create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
    union all
    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select ltrim(x) [value], [no] [index] from r where x is not null;
go

Verwendung:

select *
from [dbo].[SplitStringToResultSet]('Hallo John Smith', ' ')
where [index] = 1;

Ergebnis:

value   index
-------------
John    1

Answer 3

Fast alle anderen Antworten ersetzen den zu trennenden String, was CPU-Zyklen verschwendet und unnötige Speicherzuweisungen durchführt.

Ich zeige hier einen viel besseren Weg, um einen String zu trennen: http://www.digitalruby.com/split-string-sql-server/

Hier ist der Code:

SET NOCOUNT ON

-- Sie sollten nvarchar(MAX) in nvarchar(50), varchar(50) oder was auch immer genau mit der Zeilenspalte übereinstimmt, die Sie durchsuchen möchten, ändern
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'dein|zu|trennender|String|hier'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
BEGIN
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
    INSERT @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)

SET NOCOUNT OFF

-- Sie können zu diesem Zeitpunkt die Werte in @SplitStringTable auswählen oder damit verknüpfen.

Answer 4

Ich weiß, es ist eine alte Frage, aber ich denke, jemand kann von meiner Lösung profitieren.

select 
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,1
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
    ,LEN(column_name))
from table_name

SQL FIDDLE

Vorteile:

• Es trennt alle 3 Teilzeichenfolgen durch ' '.
• Es muss keine Schleife verwendet werden, da dies die Leistung beeinträchtigt.
• Es ist nicht notwendig zu pivotieren, da alle resultierenden Teilzeichenfolgen in einer Zeile angezeigt werden.

Einschränkungen:

• Es muss die Gesamtanzahl der Leerzeichen (Teilzeichenfolgen) bekannt sein.

Hinweis: Die Lösung kann Teilzeichenfolgen bis zu bis zu N liefern.

Um die Einschränkung zu überwinden, können wir die folgende Referenz verwenden.

Aber auch die obige Lösung kann nicht in einer Tabelle verwendet werden (eigentlich konnte ich sie nicht verwenden).

Noch hoffe ich, dass diese Lösung jemandem helfen kann.

Aktualisierung: Im Falle von Datensätzen > 50000 ist es nicht ratsam, SCHLEIFEN zu verwenden, da dies die Leistung beeinträchtigt.

Answer 5

Beginnend mit SQL Server 2016 verwenden wir string_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')