0

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(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

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

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

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

NWil
  • 9
  • 1
  • 6
  • 2
    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 Jun 28 '18 at 20:54
  • 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 Jun 28 '18 at 21:00

3 Answers3

2

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:

Column1 Column2                Column3
AB      1234                   10
AB      ANYNUMBEROF-CHARACTERS 10
Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
2

One approach is to use 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.

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.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • 1
    I like this trick a lot but it fails miserably if any of the source strings contains a dot. – Cee McSharpface Jun 28 '18 at 21:00
  • 2
    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 Jun 28 '18 at 21:03
  • 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 Jun 28 '18 at 23:02
1

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
Eric
  • 3,165
  • 1
  • 19
  • 25
  • 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 Jun 28 '18 at 23:01