get all Values separated by delimiter in sql

In this blog, I will discuss about “How to get all values separated by delimiter in SQL”. In one of my previous blog, where I have explained about positions of all delimiters present in a string. We will use same logic here as well. For example I have string with value;;123@gmail,com
In the above string, I have three email ids which are separated by delimiter ; . I need these values in below format

Use the below code to fetch the data in above format .

declare @var1 varchar(100) =’;;123@gmail,com’
;with T as (
select 0 as row_id, charindex(‘;’, @var1) pos
union all
select pos + 1, charindex(‘;’, @var1, pos + 1)
from T
where pos > 0)
select case when row_id=0 then SUBSTRING(@var1,1,pos-1)
when pos=0 then SUBSTRING(@var1,row_id,len(@var1))
SUBSTRING(@var1, row_id,pos-row_id )end from T

You can use this code in case of Mobile number or Patient Name or SSN or Medical Record number separated by Delimiter.

Related posts