Click here to Skip to main content
14,329,993 members
Rate this:
Please Sign up or sign in to vote.
Hi All,

I have a string variable in sql and i want to split with multiple characters Delimiter .

DECLARE @STR = VARCHAR(100)
SET @STR = 'ABC***PQR***XYZ***FIRST NAME***MOBILE NO.'

and i want to result like this:-

Items:
ABC
PQR
XYZ
FIRST NAME
MOBILE NO.

Please Help....

Thanks in advance...
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

It's a poor idea - SQL is not good at string manipulations, that kind ofthing should be done in your presentation code, or better stored in your DB as separate fields to start with.

But it is possible: Converting comma separated data in a column to rows for selection[^] does it for a single comma, but it's pretty trivial to modify for three stars.
   
Comments
PravinPrajapati 28-Apr-15 6:03am
   
Thank you for a reply but i don't have any other solutions so i need to do this from sql server side so please give a solution.

Thank you.
OriginalGriff 28-Apr-15 6:09am
   
Did you look at the link I gave you?
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hi,

Try this

DECLARE @STR  VARCHAR(100)
SET @STR = 'ABC***PQR***XYZ***FIRST NAME***MOBILE NO.'

;with cte as
(
    Select CAST( '<i>'+replace (@STR ,'***','</i><i>')+'</i>'  as XML) as word

)
Select x.i.value ('.','nvarchar(100)')  as Items
from cte
cross apply word.nodes('/i') as X(i)


output

Items
ABC
PQR
XYZ
FIRST NAME
MOBILE NO.
   
Comments
PravinPrajapati 30-Apr-15 6:34am
   
Great Solution
Thanks. It's Work for me...

Thank You..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100