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

Versuche dies:

CREATE function [SplitWordList]
(
 @list varchar(8000)
)
returns @t table 
(
 Word varchar(50) not null,
 Position int identity(1,1) not null
)
as begin
  declare 
    @pos int,
    @lpos int,
    @item varchar(100),
    @ignore varchar(100),
    @dl int,
    @a1 int,
    @a2 int,
    @z1 int,
    @z2 int,
    @n1 int,
    @n2 int,
    @c varchar(1),
    @a smallint
  select 
    @a1 = ascii('a'),
    @a2 = ascii('A'),
    @z1 = ascii('z'),
    @z2 = ascii('Z'),
    @n1 = ascii('0'),
    @n2 = ascii('9')
  set @ignore = '''"'
  set @pos = 1
  set @dl = datalength(@list)
  set @lpos = 1
  set @item = ''
  while (@pos <= @dl) begin
    set @c = substring(@list, @pos, 1)
    if (@ignore not like '%' + @c + '%') begin
      set @a = ascii(@c)
      if ((@a >= @a1) and (@a <= @z1))  
        or ((@a >= @a2) and (@a <= @z2))
        or ((@a >= @n1) and (@a <= @n2))
      begin
        set @item = @item + @c
      end else if (@item > '') begin
        insert into @t values (@item)
        set @item = ''
      end
    end 
    set @pos = @pos + 1
  end
  if (@item > '') begin
    insert into @t values (@item)
  end
  return
end

Testen Sie es wie folgt:

select * from SplitWordList('Hello John Smith')

Answer 2

Das folgende Beispiel verwendet eine rekursive CTE

Update 18.09.2013

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END

Demo auf SQLFiddle

Answer 3

SQL Server 2022 unterstützt die folgende Signatur von STRING_SPLIT:

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Wenn das enable_ordinal-Flag auf 1 gesetzt ist, wird das Ergebnis eine Spalte namens ordinal enthalten, die die 1basierte Position des Teilstrings innerhalb des Eingabestrings darstellt:

SELECT *
FROM STRING_SPLIT('hallo john smith', ' ', 1)

| value | ordinal |
|-------|---------|
| hallo | 1       |
| john  | 2       |
| smith | 3       |

Dies ermöglicht es uns, dies zu tun:

SELECT value
FROM STRING_SPLIT('hallo john smith', ' ', 1)
WHERE ordinal = 2

| value |
|-------|
| john  |

Oder das:

SELECT str, substr
FROM (VALUES
  ('hallo john smith'),
  ('hallo jane'),
  ('hallo')
) AS t(str)
OUTER APPLY (
  SELECT value
  FROM STRING_SPLIT(str, ' ', 1)
  WHERE ordinal = 2
) AS a(substr)

| str              | substr |
|------------------|--------|
| hallo john smith | john   |
| hallo jane       | jane   |
| hallo            | null   |

Answer 4

Sie können einen String in SQL teilen, ohne eine Funktion zu benötigen:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('' + REPLACE(@bla, ',', '') + '' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

Wenn Sie beliebige Zeichenfolgen unterstützen müssen (mit XML-Sonderzeichen)

DECLARE @bla NVARCHAR(MAX)
SET @bla = 'unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex",Barnes & Noble,abc,def,ghi'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
FROM 
(
    SELECT 
    CAST('' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '') + '' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

Answer 5

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