Используя T-SQL, верните n-й разделительный элемент из строки

Ответ 1

Вот мое первоначальное решение... Он основан на работе Аарона Бертранга http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Я просто изменил тип возвращаемого значения, чтобы сделать его скалярной функцией.

Пример: SELECT dbo.GetSplitString_CTE ('1,222,2,67,888,1111', ',', 4)

CREATE FUNCTION dbo.GetSplitString_CTE
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN

   DECLARE @result varchar(4000)    
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  

   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);  

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end][email protected])
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   SELECT @result=Item
   FROM @Items
   WHERE [email protected]

   RETURN @result;
END
GO

Ответ 2

Это самый простой ответ, чтобы восстановить 67 (типобезопасный !!):

SELECT CAST('<x>' + REPLACE('1,222,2,67,888,1111',',','</x><x>') + '</x>' AS XML).value('/x[4]','int')

В следующем вы найдете примеры, как использовать это с переменными для строки, разделителя и позиции (даже для крайних случаев с символами, запрещенными для XML)

Легкий

Этот вопрос не о подходе разделения строк, а о , как получить n-й элемент. Самый простой, полностью встроенный способ - это IMO:

Это настоящий однострочный для получения части 2, разделенной пробелом:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

Переменные можно использовать с sql:variable() или sql:column()

Конечно, вы можете использовать переменные для разделителя и позиции (используйте sql:column, чтобы получить позицию непосредственно из значения запроса):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

Пограничный регистр с символами, запрещенными для XML

Если ваша строка может содержать запрещенные символы, вы все равно можете сделать это следующим образом. Просто используйте сначала FOR XML PATH в вашей строке, чтобы неявно заменить все запрещенные символы подходящей escape-последовательностью.

Это особый случай, если - дополнительно - ваш разделитель - точка с запятой. В этом случае сначала я заменяю разделитель на "# DLMT #", и окончательно заменяю его тегами XML:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

ОБНОВЛЕНИЕ для SQL-сервера 2016+

К сожалению, разработчики забыли вернуть индекс детали с помощью STRING_SPLIT. Но, используя SQL-сервер 2016+, есть JSON_VALUE и OPENJSON.

С помощью JSON_VALUE мы можем передать позицию в виде массива индекса.

Для OPENJSON документация documentation четко гласит:

Когда OPENJSON анализирует массив JSON, функция возвращает индексы элементов в тексте JSON в качестве ключей.

Строка типа 1,2,3 не требует ничего, кроме скобок: [1,2,3].
Строка слов вроде this is an example должна быть ["this","is","an"," example"].
Это очень простые строковые операции. Просто попробуйте:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

--See это для безопасного разделения строк (с нуля):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

В этом посте я протестировал различные подходы и обнаружил, что OPENJSON действительно быстр. Даже намного быстрее, чем знаменитый метод delimitedSplit8k()...

ОБНОВЛЕНИЕ 2 - Получить значения типа безопасными

Мы можем использовать массив внутри массива просто используя doubled [[]]. Это позволяет печатать WITH -clause:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
    ,TheSecondFragment INT '$[1]'
    ,TheThirdFragment DATE '$[2]') ValuesFromTheArray

Ответ 3

Как насчет:

CREATE FUNCTION dbo.NTH_ELEMENT (@Input NVARCHAR(MAX), @Delim CHAR = '-', @N INT = 0)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (SELECT VALUE FROM STRING_SPLIT(@Input, @Delim) ORDER BY (SELECT NULL) OFFSET @N ROWS FETCH NEXT 1 ROW ONLY)
END

Ответ 4

@a - значение (например, 'a/bb/ccc/dddd/ee/ff/....')

@p - желаемая позиция (1,2,3...)

@d - разделитель ('/')

обрезать (подстрока (заменить (@a, @d, повторить ('', len (@a))), (@p-1) * len (@a) +1, len (@a)))

Единственная проблема заключается в том, что если у нужной детали есть задние или ведущие заготовки, они обрезаются.

Полностью основано на статье из https://exceljet.net/formula/split-text-with-delimiter

Ответ 5

В редкий момент безумия я просто подумал, что раскол намного проще, если мы используем XML для его анализа:

(Используя переменные из ответа @Gary Kindel)

declare @xml xml
set @xml = '<split><el>' + replace(@list,@Delimiter,'</el><el>') + '</el></split>'

select
    el = split.el.value('.','varchar(max)')
from  @xml.nodes('/split/el') split(el))

Здесь перечислены все элементы строки, разделенные указанным символом.

Мы можем использовать тест xpath, чтобы отфильтровать пустые значения, и еще один тест xpath, чтобы ограничить это элементом, который нас интересует. В полной функции Гэри становится:

alter FUNCTION dbo.GetSplitString_CTE
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(max)
AS
BEGIN

       declare @xml xml
       set @xml = '<split><el>' + replace(@list,@Delimiter,'</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

Ответ 6

вы можете поместить этот выбор в UFN. если вам нужно, вы можете настроить его и для указания разделителя. в этом случае ваш ufn будет иметь два входа. номер N и разделитель.

    DECLARE @tlist varchar(max)='10,20,30,40,50,60,70,80,90,100'
    DECLARE @i INT=1, @nth INT=3
    While len(@tlist) <> 0
    BEGIN
            IF @[email protected]
            BEGIN
              select Case when charindex(',',@tlist) <> 0 Then LEFT(@tlist,charindex(',',@tlist)-1)
                          Else @tlist
                    END
            END

              Select @tlist = Case when charindex(',',@tlist) <> 0 Then substring(@tlist,charindex(',',@tlist)+1,len(@tlist))
                          Else ''
                          END

            SELECT @[email protected]+1
    END

Ответ 7

Я бы предпочел создать временную таблицу со столбцом идентификаторов и заполнить ее выводом из функции SPLIT.

  CREATE TABLE #tblVals(Id INT IDENTITY(1,1), Val NVARCHAR(100))
  INSERT INTO #tblVals (Val)
  SELECT [value] FROM STRING_SPLIT('Val1-Val3-Val2-Val5', '-')
  SELECT * FROM #tblVals

Теперь вы можете легко сделать что-то вроде ниже.

DECLARE @val2 NVARCHAR(100) = (SELECT TOP 1 Val FROM #tblVals WHERE Id = 2)

Смотрите снимок ниже:

Ответ 8

Я не могу комментировать решение Gary из-за моей низкой репутации

Я знаю, что Гэри ссылался на другую ссылку.

Я изо всех сил пытался понять, зачем нужна эта переменная

@ld INT = LEN(@Delimiter)

Я также не понимаю, почему charindex должен начинаться с позиции длины разделителя, @ld

Я тестировал множество примеров с одним символьным разделителем, и они работают. В большинстве случаев символ разделителя является единственным символом. Однако, поскольку разработчик включил ld в качестве длины разделителя, код должен работать для разделителей, которые имеют более одного символа.

В этом случае следующий случай не будет выполнен

11,, 22,, 33,, 44,,, 55

Я клонировал из кодов из этой ссылки. http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/

Я тестировал различные сценарии, включая разделители, которые имеют более одного символа.

alter FUNCTION [dbo].[split1]
(
    @string1 VARCHAR(8000) -- List of delimited items
    , @Delimiter VARCHAR(40) = ',' -- delimiter that separates items
    , @ElementNumber int
)
RETURNS varchar(8000)
AS
BEGIN
    declare @position int
    declare @piece varchar(8000)=''
    declare @returnVal varchar(8000)=''
    declare @Pattern varchar(50) = '%' + @Delimiter + '%'
    declare @counter int =0
    declare @ld int = len(@Delimiter)
    declare @ls1 int = len (@string1)
    declare @foundit int = 0

    if patindex(@Pattern , @string1) = 0
        return  ''

    if right(rtrim(@string1),1) <> @Delimiter
        set @string1 = @string1  + @Delimiter

    set @position =  patindex(@Pattern , @string1) + @ld  -1  
    while @position > 0
    begin
        set @counter = @counter +1 
        set @ls1  = len (@string1)
        if (@ls1 >= @ld)
            set @piece = left(@string1, @position - @ld)
        else
            break
        if (@counter = @ElementNumber)
        begin
            set @foundit = 1
                break
        end
        if len(@string1) > 0
        begin
            set @string1 = stuff(@string1, 1, @position, '')
            set @position =  patindex(@Pattern , @string1) + @ld  -1  
        end
        else
        set @position = -1
    end 


    if @foundit =1
        set @returnVal = @piece
    else 
        set @returnVal =  ''
    return @returnVal

Ответ 9

В качестве альтернативы можно использовать xml, nodes() и ROW_NUMBER. Мы можем заказать элементы в соответствии с порядком их документов. Например:

DECLARE @Input VARCHAR(100) = '1a,2b,3c,4d,5e,6f,7g,8h'
       ,@Number TINYINT = 3

DECLARE @XML XML;
DECLARE @value VARCHAR(100);

SET @XML = CAST('<x>' + REPLACE(@Input,',','</x><x>') + '</x>' AS XML);

WITH DataSource ([rowID], [rowValue]) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY T.c ASC) 
            ,T.c.value('.', 'VARCHAR(100)')
    FROM @XML.nodes('./x') T(c)
)
SELECT @value = [rowValue]
FROM DataSource
WHERE [rowID] = @Number;

SELECT @value;

Ответ 10

У меня недостаточно репутации для комментариев, поэтому я добавляю ответ. При необходимости отрегулируйте.

У меня проблема с ответом Гэри Кинделя для случаев, когда между двумя разделителями

Если вы это сделаете выберите * из dbo.GetSplitString_CTE ('abc ^ def ^^ ghi', '^', 3) Вы получаете ГХИ вместо пустой строки

Если вы закомментируете  WHERE LEN ([значение]) > 0 line, вы получите желаемый результат