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 abc@gmail.com;xyz@gmail.com;123@gmail,com
In the above string, I have three email ids which are separated by delimiter ; . I need these values in below format
abc@gmail.com
xyz@gmail.com
123@gmail,com
Use the below code to fetch the data in above format .
declare @var1 varchar(100) =’abc@gmail.com;xyz@gmail.com;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))
else
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.