0

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.

Edited by GL.Za: n/a

3
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by GL.Za
0

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

Edited by mike_2000_17: Fixed formatting

1

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

Edited by tesuji: n/a

Votes + Comments
Your solution made effective use of standard exosting functions, thanks.
0

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.

Edited by GL.Za: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.