T-SQL split string based on delimiter
222,644
Solution 1
May be this will help you.
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
Solution 2
For those looking for answers for SQL Server 2016+. Use the built-in STRING_SPLIT function
Eg:
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
Reference: https://msdn.microsoft.com/en-nz/library/mt684588.aspx
Solution 3
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
Solution 4
Try filtering out the rows that contain strings with the delimiter and work on those only like:
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable
WHERE CHARINDEX('/', myColumn) > 0
Or
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable
WHERE myColumn LIKE '%/%'
Solution 5
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
Author by
Sesame
Updated on July 11, 2022Comments
-
Sesame almost 2 years
I have some data that I would like to split based on a delimiter that may or may not exist.
Example data:
John/Smith Jane/Doe Steve Bob/Johnson
I am using the following code to split this data into First and Last names:
SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName, SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName FROM MyTable
The results I would like:
FirstName---LastName John--------Smith Jane--------Doe Steve-------NULL Bob---------Johnson
This code works just fine as long as all the rows have the anticipated delimiter, but errors out when a row does not:
"Invalid length parameter passed to the LEFT or SUBSTRING function."
How can I re-write this to work properly?
-
ripvlan about 6 yearsI'm not sure this answers the question nor is it as useful as the CASE statement methods above. The STRING_SPLIT function turns it into a Table that is joined. So how does one get the first-position "First name" the second "Last Name" ? Using Cross-Apply transformed my ds into multiple rows. It's a great way transform String data into a table. Like the OP, I'm trying to transform a single column into two columns. STRING_SPLIT turns it into two rows and no way to tell which row is the "first" "second" "third" (unless Over works). But that becomes more complicated.