Разделительная строка T-SQL на основе разделителя

У меня есть некоторые данные, которые я хотел бы разбить на основе разделителя, который может или не может существовать.

Пример данных:

John/Smith
Jane/Doe
Steve
Bob/Johnson

Я использую следующий код для разделения этих данных на имена First и Last:

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable

Результаты, которые мне бы хотелось:

FirstName---LastName
John--------Smith
Jane--------Doe
Steve-------NULL
Bob---------Johnson

Этот код работает просто отлично, пока все строки имеют ожидаемый разделитель, но ошибки, когда строка не работает:

"Invalid length parameter passed to the LEFT or SUBSTRING function."

Как перезаписать это для правильной работы?

Ответы

Ответ 1

Возможно, это поможет вам.

SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn)
            ELSE CHARINDEX('/', myColumn) - 1
            END) AS FirstName
    ,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn) + 1
            ELSE CHARINDEX('/', myColumn) + 1
            END, 1000) AS LastName
FROM MyTable

Ответ 2

SELECT CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN myColumn
        ELSE LEFT(myColumn, CHARINDEX('/', myColumn, 0)-1)
        END AS FirstName
    ,CASE 
        WHEN CHARINDEX('/', myColumn, 0) = 0
            THEN ''
        ELSE RIGHT(myColumn, CHARINDEX('/', REVERSE(myColumn), 0)-1)
        END AS LastName
FROM MyTable

Ответ 3

Для тех, кто ищет ответы на SQL Server 2016+. Используйте встроенную функцию STRING_SPLIT

Например:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

Ссылка: https://msdn.microsoft.com/en-nz/library/mt684588.aspx

Ответ 4

Попробуйте отфильтровать строки, содержащие строки с разделителем, и работать с такими, как:

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE CHARINDEX('/', myColumn) > 0

или

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE myColumn LIKE '%/%'

Ответ 5

Приведенные выше примеры отлично работают, когда есть только один разделитель, но он не подходит для нескольких разделителей. Обратите внимание, что это будет работать только для SQL Server 2016 и выше.

/*Some Sample Data*/
DECLARE @mytable TABLE ([id] VARCHAR(10), [name] VARCHAR(1000));
INSERT INTO @mytable
VALUES ('1','John/Smith'),('2','Jane/Doe'), ('3','Steve'), ('4','Bob/Johnson')


/*Split based on delimeter*/
SELECT P.id, [1] 'FirstName', [2] 'LastName', [3] 'Col3', [4] 'Col4'
FROM(
    SELECT A.id, X1.VALUE, ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY A.id) RN
    FROM @mytable A
    CROSS APPLY STRING_SPLIT(A.name, '/') X1
    ) A
PIVOT (MAX(A.[VALUE]) FOR A.RN IN ([1],[2],[3],[4],[5])) P

Ответ 6

'ALTER FUNCTION [dbo]. [Split_string] (@delimited NVARCHAR (MAX), @delimiter NVARCHAR (100)) ВОЗВРАЩАЕТСЯ @T TABLE (id INT IDENTITY (1,1), val NVARCHAR (MAX)) КАК НАЧИНАЕТСЯ @xml XML SET @xml = N '' + REPLACE (@delimited, @delimiter, '') + ''

INSERT INTO @t (val) ВЫБРАТЬ r.value('.', 'Varchar (MAX)') в качестве элемента FROM @xml.nodes('/t') в качестве records (r) RETURN END '