Разделить строку T-SQL на - и пробел

У меня проблемы с T-SQL, и мне было интересно, может ли кто-нибудь указать мне правильный путь. У меня есть следующая переменная с именем @input

    DECLARE @input nvarchar(100);
    SET @input= '27364 - John Smith';
   -- SET @input= '27364 - John Andrew Smith';

Мне нужно разделить эту строку на 3 части (идентификатор, имя и фамилия) или 4, если строка содержит отчество. Из соображений безопасности я не могу использовать functions.

Мой подход заключался в использовании Substring и Charindex.

SET @Id = SUBSTRING(@input, 1, CASE CHARINDEX('-', @input)
                    WHEN 0
                        THEN LEN(@input)
                    ELSE 
                        CHARINDEX('-', @input) - 2
                    END);
        SET @FirstName = SUBSTRING(@input, CASE CHARINDEX(' ', @input)
                    WHEN 0
                        THEN LEN(@input) + 1
                    ELSE 
                        CHARINDEX(' ', @input) + 1
                    END, 1000);
        SET @LastName = SUBSTRING(@input, CASE CHARINDEX(' ', @input)
                    WHEN 0
                        THEN LEN(@input) + 1
                    ELSE 
                        CHARINDEX('0', @input) + 1
                    END, 1000);
Select @PartyCode,@FirstName,@LastName

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

есть идеи?

заранее спасибо

tsql split charindex

Toto07 07.07.2016 источник

comment

For security reason I cannot use functions. что это значит? LEN() и SUBSTRING — это функции - Panagiotis Kanavos 07.07.2016

comment

Я предполагаю, что создание функций/изменение схемы отключено для Toto07 по безопасности. Таким образом, создание функции было бы бесполезным. И CLR тоже отсутствует. - TamusJRoyce 22.08.2017

Ответы (2)

arrow_upward
4
arrow_downward

Надеюсь, это часть проекта нормализации. Эти данные нарушают 1NF, и этого действительно следует избегать...

Попробуйте это так

Преимущества

типизированные значения
специальный SQL
установить на основе

Если вы хотите, вы можете использовать CASE WHEN, чтобы проверить, является ли последняя часть NULL, и в этом случае поместить Part2 в Part3...

DECLARE @input table(teststring nvarchar(100));
INSERT INTO @input VALUES
(N'27364 - John Smith'),(N'27364 - John Andrew Smith');

WITH Splitted AS
(
    SELECT CAST(N'<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(teststring,N' - ',N' '),N'&',N'&amp;'),N'<',N'&lt;'),N'>',N'&gt;'),N' ',N'</x><x>') + N'</x>' AS XML) testXML
    FROM @input
)
SELECT testXML.value('/x[1]','int') AS Number
      ,testXML.value('/x[2]','nvarchar(max)') AS Part1 
      ,testXML.value('/x[3]','nvarchar(max)') AS Part2 
      ,testXML.value('/x[4]','nvarchar(max)') AS Part3 
FROM Splitted

Результат

Number  Part1   Part2   Part3
27364   John    Smith   NULL
27364   John    Andrew  Smith

Shnugo 07.07.2016

comment

SQL Server 2016 добавляет функцию STRING_SPLIT, но опять же, это не должно использоваться в качестве предлога для нарушения 1NF. - Panagiotis Kanavos; 07.07.2016

comment

@PanagiotisKanavos, в этом случае новая функция разделения добавит дополнительную работу, поскольку она возвращает части в виде отдельных строк, и вам придется использовать PIVOT и CAST, чтобы добиться того же. Уничтожая это с помощью XML, вы можете получить каждую часть безопасной для типов и прямой... - Shnugo; 07.07.2016

arrow_upward
0
arrow_downward

SQL Server 2016 имеет новую встроенную функцию STRING_SPLIT().

Предполагая создание встроенных функций, но функции CLR не разрешены:

CREATE FUNCTION dbo.WORD_SPLIT
(   
    @String AS nvarchar(4000)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Spaces AS
    (
        SELECT Spaced.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY (SELECT 1)) AS ordinal
        FROM STRING_SPLIT(@String, ' ') AS Spaced
    )
    , Tabs AS
    (
        SELECT Tabbed.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY s.ordinal, (SELECT 1)) AS ordinal
        FROM Spaces AS s
            CROSS APPLY STRING_SPLIT(s.[value], '   ') AS Tabbed
    )
    , NewLines1 AS
    (
        SELECT NewLined1.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY t.ordinal, (SELECT 1)) AS ordinal
        FROM Tabs AS t
            CROSS APPLY STRING_SPLIT(t.[value], CHAR(13)) AS NewLined1
    )
    , NewLines2 AS
    (
        SELECT NewLined2.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY nl1.ordinal, (SELECT 1)) AS ordinal
        FROM NewLines1 AS nl1
            CROSS APPLY STRING_SPLIT(nl1.[value], CHAR(10)) AS NewLined2
    )
    SELECT LTRIM(RTRIM(nl2.[value])) AS [value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY nl2.ordinal, (SELECT 1)) AS ordinal
    FROM NewLines2 AS nl2
    WHERE LTRIM(RTRIM(nl2.[value])) <> ''
)
GO

Использование:

-- Not Normailized
SELECT i.*, split.[value], split.[ordinal]
FROM @input AS i
    CROSS APPLY dbo.WORD_SPLIT(i.teststring) AS split

-- Normalized
;WITH Splitted AS
(
    SELECT split.[value], split.[ordinal]
    FROM @input AS i
        CROSS APPLY dbo.WORD_SPLIT(i.teststring) AS split
)
SELECT *
FROM (SELECT [value], 'part' + CONVERT(nvarchar(20), [ordinal]) AS [parts] FROM Splitted) AS s
    PIVOT (MAX([value]) FOR [parts] IN ([part1], [part2], [part3], [part4])

Или предполагая, что для каждой безопасности вам не разрешено вносить изменения в схему:

WITH Splitting AS
(
    SELECT teststring AS [value]
    FROM @input
)
WITH Spaces AS
(
    SELECT Spaced.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY (SELECT 1)) AS ordinal
    FROM Splitting AS sp
        CROSS APPLY STRING_SPLIT(sp.[value], ' ') AS Spaced
)
, Tabs AS
(
    SELECT Tabbed.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY s.ordinal, (SELECT 1)) AS ordinal
    FROM Spaces AS s
        CROSS APPLY STRING_SPLIT(s.[value], '   ') AS Tabbed
)
, NewLines1 AS
(
    SELECT NewLined1.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY t.ordinal, (SELECT 1)) AS ordinal
    FROM Tabs AS t
        CROSS APPLY STRING_SPLIT(t.[value], CHAR(13)) AS NewLined1
)
, NewLines2 AS
(
    SELECT NewLined2.[value], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY nl1.ordinal, (SELECT 1)) AS ordinal
    FROM NewLines1 AS nl1
        CROSS APPLY STRING_SPLIT(nl1.[value], CHAR(10)) AS NewLined2
)
, Splitted AS
(
    SELECT LTRIM(RTRIM(nl2.[value])) AS [teststring], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY nl2.ordinal, (SELECT 1)) AS ordinal
    FROM NewLines2 AS nl2
    WHERE LTRIM(RTRIM(nl2.[value])) <> ''
)
SELECT *
FROM (SELECT [value], 'part' + CONVERT(nvarchar(20), [ordinal]) AS [parts] FROM Splitted) AS s
    PIVOT (MAX([value]) FOR [parts] IN ([part1], [part2], [part3], [part4])

Надеюсь, полезно!

TamusJRoyce 22.08.2017

comment

Я согласен, что ответ Shnugo лучше подходит для этого поворотного сценария. Это хорошо, чтобы проверить альтернативы для вашей ситуации. - TamusJRoyce; 22.08.2017

Разделить строку T-SQL на - и пробел

Ответы (2)

Вопросы по теме