T-SQL gesplitste tekenreeks op basis van scheidingsteken

Ik heb enkele gegevens die ik wil splitsen op basis van een scheidingsteken dat al dan niet bestaat.

Voorbeeldgegevens:

John/Smith
Jane/Doe
Steve
Bob/Johnson

Ik gebruik de volgende code om deze gegevens op te splitsen in voor- en achternamen:

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

De resultaten die ik zou willen:

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

Deze code werkt prima zolang alle rijen het verwachte scheidingsteken hebben, maar er treden fouten op als een rij dat niet doet:

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

Hoe kan ik dit opnieuw schrijven om correct te werken?


Antwoord 1, autoriteit 100%

Misschien zal dit je helpen.

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

Antwoord 2, autoriteit 17%

Voor wie op zoek is naar antwoorden voor SQL Server 2016+. Gebruik de ingebouwde STRING_SPLIT-functie

Bijvoorbeeld:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';  

Referentie: https://msdn.microsoft.com/en-nz/ bibliotheek/mt684588.aspx


Antwoord 3, autoriteit 12%

Probeer de rijen die strings bevatten uit te filteren met het scheidingsteken en werk alleen aan die zoals:

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

Of

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

Antwoord 4, autoriteit 11%

SELECTEER GEVAL
    WHEN CHARINDEX('/', myColumn, 0) = 0
      DAN mijnKolom
    ELSE LEFT(mijnkolom, CHARINDEX('/', mijnkolom, 0)-1)
    EINDE ALS Voornaam
  ,GEVAL
    WHEN CHARINDEX('/', myColumn, 0) = 0
      DAN ''
    ANDERS RECHTS(mijnkolom, CHARINDEX('/', REVERSE(mijnkolom), 0)-1)
    EINDE ALS Achternaam
VAN MijnTabel

Antwoord 5, autoriteit 3%

ALTER FUNCTION [dbo].[split_string](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

Antwoord 6, autoriteit 3%

Ik wilde alleen een alternatieve manier geven om een ​​string met meerdere scheidingstekens te splitsen, voor het geval je een SQL Server-versie onder 2016 gebruikt.

Het algemene idee is om alle tekens in de tekenreeks op te splitsen, de positie van de scheidingstekens te bepalen en vervolgens subtekenreeksen te verkrijgen ten opzichte van de scheidingstekens. Hier is een voorbeeld:

-- Sample data
DECLARE @testTable TABLE (
    TestString      VARCHAR(50)
)
INSERT INTO @testTable VALUES 
    ('Teststring,1,2,3')
    ,('Test')
DECLARE @delimiter VARCHAR(1) = ','
-- Generate numbers with which we can enumerate
;WITH Numbers AS (
    SELECT 1 AS N
    UNION ALL 
    SELECT N + 1
    FROM Numbers 
    WHERE N < 255
), 
-- Enumerate letters in the string and select only the delimiters
Letters AS (
    SELECT  n.N
            , SUBSTRING(t.TestString, n.N, 1) AS Letter
            , t.TestString 
            , ROW_NUMBER() OVER (   PARTITION BY t.TestString
                                    ORDER BY n.N
                                ) AS Delimiter_Number 
    FROM Numbers n
        INNER JOIN @testTable t
            ON n <= LEN(t.TestString)
    WHERE SUBSTRING(t.TestString, n, 1) = @delimiter 
    UNION 
    -- Include 0th position to "delimit" the start of the string
    SELECT  0
            , NULL
            , t.TestString 
            , 0
    FROM @testTable t 
)
-- Obtain substrings based on delimiter positions
SELECT  t.TestString 
        , ds.Delimiter_Number + 1 AS Position
        , SUBSTRING(t.TestString, ds.N + 1, ISNULL(de.N, LEN(t.TestString) + 1) - ds.N - 1) AS Delimited_Substring 
FROM @testTable t
    LEFT JOIN Letters ds
        ON t.TestString = ds.TestString 
    LEFT JOIN Letters de
        ON t.TestString = de.TestString 
        AND ds.Delimiter_Number + 1 = de.Delimiter_Number  
OPTION (MAXRECURSION 0)

Antwoord 7

De bovenstaande voorbeelden werken prima als er maar één scheidingsteken is, maar het schaalt niet goed voor meerdere scheidingstekens. Merk op dat dit alleen werkt voor SQL Server 2016 en hoger.

/*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

Antwoord 8

Dit heeft me allemaal geholpen om dit te bereiken. Ik ben nog steeds op 2012, maar heb nu iets snels waarmee ik een string kan splitsen, zelfs als string een verschillend aantal scheidingstekens heeft, en de n-de substring van die string kan pakken. Het is ook snel. Ik weet dat dit bericht oud is, maar het kostte me een eeuwigheid om iets te vinden, dus hopelijk zal dit iemand anders helpen.

CREATE FUNCTION [dbo].[SplitsByIndex]
(@separator VARCHAR(20)  = ' ', 
 @string    VARCHAR(MAX), 
 @position  INT
)
RETURNS VARCHAR(MAX)
AS
     BEGIN
     DECLARE @results TABLE
     (id   INT IDENTITY(1, 1), 
      chrs VARCHAR(8000)
     );
     DECLARE @outResult VARCHAR(8000);
     WITH X(N)
          AS (SELECT 'Table1'
              FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)),
          Y(N)
          AS (SELECT 'Table2'
              FROM X A1, 
                   X A2, 
                   X A3, 
                   X A4, 
                   X A5, 
                   X A6, 
                   X A7, 
                   X A8), -- Up to 16^8 = 4 billion
          T(N)
          AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER(
                                                   ORDER BY
              (
                  SELECT NULL
              )) - 1 N
              FROM Y),
          Delim(Pos)
          AS (SELECT t.N
              FROM T
              WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator
                    OR t.N = 0)),
          Separated(value)
          AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER(
                     ORDER BY
              (
                  SELECT NULL
              ))-d.Pos - LEN(@separator))
              FROM Delim d
              WHERE @string IS NOT NULL)
          INSERT INTO @results(chrs)
                 SELECT s.value
                 FROM Separated s
                 WHERE s.value <> @separator;
     SELECT @outResult =
     (
         SELECT chrs
         FROM @results
         WHERE id = @position
     );
     RETURN @outResult;
 END;

Dit kan als volgt worden gebruikt:

SELECT [dbo].[SplitsByIndex](' ',fieldname,2) 
from tablename

Other episodes