asked on

Split a delimited string in SQL

Hey Experts,

I am trying to figure out how to break out a string in SQL. The column is called ID_SUB and can vary in lenght and in number of delimiters but no more than four levels. I need to get the information between the delimiters into levels. Here are some examples of strings i need to work with and what needs to be returned.

3.4
2
12.5.25.1
2.2.1
124.12.1.54

and would return:

L1 L2 L3 L4
-------------------
3 4
2
12 5 25 1
2 2 1
124 12 1 54

SELECT SUBSTRING(ID_SUB,0,CHARINDEX('.',ID_SUB)) as LVL1,  
???? AS LVL2
???? AS LVL3
???? AS LVL4
FROM TASKS
ORDER BY LVL1,LVL2,LVL3,LVL4

Open in new window

cholmesKY1

ASKER

Here's some snippets of what I've been working with....
SELECT ID_SUB,
SUBSTRING(ID_SUB,0,CHARINDEX('.',ID_SUB)) as level1,

RIGHT(ID_SUB,(LEN(ID_SUB))-LEN(SUBSTRING(ID_SUB,0,CHARINDEX('.',ID_SUB)+1))),
CHARINDEX('.',RIGHT(ID_SUB,(LEN(ID_SUB))-LEN(SUBSTRING(ID_SUB,0,CHARINDEX('.',ID_SUB)+1))))

FROM TASKS
where ID_SUB LIKE '1.%' or ID_SUB LIKE '2.%'
order by LEVEL1

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brejk

Use PARSENAME function:

SELECT
PARSENAME('1.2.3.4',4) AS Level1,
PARSENAME('1.2.3.4',3) AS Level2,
PARSENAME('1.2.3.4',2) AS Level3,
PARSENAME('1.2.3.4',1) AS Level4

ASKER CERTIFIED SOLUTION

brejk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Cool!

I am use to dealing with other delimiters and so didn't think of the fact that it is dot notation that could be treated like a qualified name...like it.

Regards,
Kevin

cholmesKY1

ASKER

Kevin & brejk: Thank you!!! As both answers work, I decided to go with brejk's answer as it is less code and will work for our application. I have given majority of points to brejk, but some to you also Kevin. I hope this is fair.

Thanks for your help with this. I am not experienced enough in SQL... I was beating my head on my desk all day friday trying to figure out a solution.

brejk

@Kevin: That trick is not mine of course :-) (I guess it has been presented somewhere in SQL Server Magazine by Itzik Ben-Gan for purpose of sorting IP addresses)

@cholmesKY1: Thx for the points :-)