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

cholmesKY1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cholmesKY1Author Commented:
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
0
Kevin CrossChief Technology OfficerCommented:
Here is an example how you are doing this.  I would suggest creating a function that breaks up the string by the delimiter '.' and stores in table variable with position and make parameters to the function the delimited string and position you want.

See here for an example of split string with occurrence.
http:/Q_23786715.html
with tasks AS (
	select '3.4' as id_sub
	union all select '2'
	union all select '12.5.25.1'
	union all select '2.2.1'
	union all select '124.12.1.54'
)
select case when charindex('.',id_sub) > 0 then left(id_sub, charindex('.',id_sub)-1) else id_sub end as LVL1
, case when charindex('.',id_sub) > 0 then case when charindex('.',id_sub, charindex('.',id_sub)+1) > 0 then substring(id_sub, charindex('.',id_sub)+1, charindex('.',id_sub, charindex('.',id_sub)+1)-charindex('.',id_sub)-1) else substring(id_sub, charindex('.',id_sub)+1, len(id_sub)-charindex('.',id_sub)) end else '' end as LVL2
, case when charindex('.',id_sub) > 0 and charindex('.',id_sub, charindex('.',id_sub)+1) > 0 then substring(id_sub, charindex('.',id_sub, charindex('.',id_sub)+1)+1, len(id_sub)) else '' end as LVL3
from tasks

Open in new window

0
brejkCommented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

brejkCommented:
In your case:
DECLARE @T TABLE (ID_SUB varchar(20))
INSERT INTO @T (ID_SUB) SELECT '3.4'
INSERT INTO @T (ID_SUB) SELECT '2'
INSERT INTO @T (ID_SUB) SELECT '12.5.25.1'
 
SELECT 
  REVERSE(PARSENAME(REVERSE(ID_SUB),1)) AS L1,
  REVERSE(PARSENAME(REVERSE(ID_SUB),2)) AS L2,
  REVERSE(PARSENAME(REVERSE(ID_SUB),3)) AS L3,
  REVERSE(PARSENAME(REVERSE(ID_SUB),4)) AS L4
FROM @T  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
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
0
cholmesKY1Author Commented:
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.
0
brejkCommented:
@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 :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.