Seperating string after specified character

[GL.Za]

Hi

I need to write a sql script to split a string in two or more parts.

e.g.
I have a string: "tag: key1, key2, key3"

I would like to use the ":" as a seperator and thus end up with two strings:
"tag" and "key1, key2, key3"

Is this possible?

I was thinking of using a function to find and return the position of the ":" and then using substr to split the string, but I dont know of any functions that return the position of a character.

Any help is much appreciated, thanks.

[jt86442]

Hi, Actually ur thread was very nice. But i have a solution by using XML in Server. u might be use .

Solution
Let's say for example there is a string 'A,B,C,D,E' and I want to split it based on the delimiter ','.

The first step would be to convert that string into XML and replace the delimiter with some start and end XML tags.

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT @xml

Here is what this looks like after the delimiter ',' is replaced by </X><X> tags. When you see the output after converting the string into XML, you will be able to see the string as shown in the image below:

Once the string is converted into XML you can easily query that using XQuery

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

Now, say I have a table that has an ID column and comma separated string data as shown below.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(50))
INSERT INTO @t(data) SELECT 'AA,AB,AC,AD'
INSERT INTO @t(data) SELECT 'BA,BB,BC'
SELECT * FROM @t

I can use the method shown above to split the string.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(50))
INSERT INTO @t(data) SELECT 'AA,AB,AC,AD'
INSERT INTO @t(data) SELECT 'BA,BB,BC'
SELECT F1.id,
 F1.data,
 O.splitdata 
FROM
 (
 SELECT *,
 cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
 )F1
 CROSS APPLY
 ( 
 SELECT fdata.D.value('.','varchar(50)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

[tesuji]

Well, if you want to do suchlike string separation in query statement, you can make use of ora's string functions, such as:

select 'tag: key1, key2, key3' as st, length(st) as le, instr(st, ':') as lo, substr(st, 1, lo-1) as tag, substr(st, lo+1, le-lo) as whateverfor;
/*
st                     le  lo tag  whateverfor
---------------------------------------------------
tag: key1, key2, key3  21  4  tag  key1, key2, key3 
*/

-- tesu

[GL.Za]

Thanks to both of you, both of you helped me.

@jt86442: Although I followed what you where doing, tesuji's method was simpler, but thanks for the effort anyway.

Thread Solved.