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