T-SQL splitting column on a delimiter [duplicate]

This question already has an answer here:

I have a column with three groups data delimited by a forward slash, like so

AB/1234/10


The column is always formatted the same in every row, with 2 characters, a slash, some number of characters, a slash, and then 2 more characters. I need to split this one column into three. So the above example becomes

Column1 Column2 Column3
AB 1234 10


I'm not quite sure how to go about this. I've been using SELECT SUBSTRING but that isn't quite giving me what I need.

SELECT SUBSTRING

select SUBSTRING(MyColumn, 1, CHARINDEX('/', MyColumn, 1)-1)
FROM MyTable


Will return AB, and that's great. But I can't wrap my mind around how to grab the middle and the end sections. I thought that

AB

select SUBSTRING(MyColumn, 4, CHARINDEX('/', MyColumn, 4))
FROM MyTable


Would work in grabbing the middle, but it returns 1234/10

1234/10

I hope my question is clear and I would appreciate any advice pointing me in the right direction, Thank you.

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Ideally part of this exercise should be to permanently separate this data. Storing multiple elements like this violates 1NF and is nothing short of painful to work with.
– Sean Lange
2 days ago

If you use a splitter like in the proposed duplicate you should avoid the accepted answer as it is a terrible way to split string. Instead scroll down to Aaron Bertrand's answer. It is much better for performance.
– Sean Lange
2 days ago

3 Answers
3

You can work with fixed offsets, since you defined that the string always starts with two, and ends with two characters.

Here is a full working example:

DECLARE @tmp TABLE (
Merged nvarchar(max)
)

INSERT INTO @tmp SELECT N'AB/1234/10'
INSERT INTO @tmp SELECT N'AB/ANYNUMBEROF-CHARACTERS/10'

SELECT
LEFT(Merged,2) AS Column1,
SUBSTRING(Merged,4,LEN(Merged)-6) AS Column2,
RIGHT(Merged,2) AS Column3
FROM @tmp


We subtract the length of the string minus a constant (6 = two chars left, two chars right, two slashes) to extract the variable-length part from the middle.

Result:

One approach is to use PARSENAME:

PARSENAME

SELECT PARSENAME(REPLACE('AB/1234/10','/','.'), 3) Col1,
PARSENAME(REPLACE('AB/1234/10','/','.'), 2) Col2,
PARSENAME(REPLACE('AB/1234/10','/','.'), 1) Col3


This will replace the / with ., and pull out each section of the string with PARSENAME.

/

.

PARSENAME

The benefit is that it will work with any length of characters in any position. The limits is that PARSENAME only handles up to 4 positions (in this case you are using 3), and will fail if periods . exist in the string already.

PARSENAME

.

I like this trick a lot but it fails miserably if any of the source strings contains a dot.
– dlatikay
2 days ago

An easy way to deal with a period is to add a replace first to swap out a . with something like ^. You would have to swap it back of course which kind of starts to get a bit over the top for this simple one.
– Sean Lange
2 days ago

Unfortunately any or all of the strings in my case can contain a . so while this answer is good, its not applicable in my case. Thanks anyway however.
– NWil
2 days ago

.

Try this. This should work with any number of characters.

DECLARE @str VARCHAR(100) = 'AB/1234/10'

SELECT LEFT(@str, CHARINDEX('/', @str) - 1) AS Column1
, SUBSTRING(@str, CHARINDEX('/', @str) + 1, CHARINDEX('/', SUBSTRING(@str, CHARINDEX('/', @str) + 1, LEN(@str))) - 1) AS Column2
, RIGHT(@str, CHARINDEX('/', REVERSE(@str)) - 1) AS Column3


If I could mark multiple answers as correct I would also mark this one, its essentially the same kind of answer that I marked at correct, but I really appreciate the robustness of it. It's much more applicable in a general use. Thank you.
– NWil
2 days ago