Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #285167] Mon, 03 December 2007 16:24 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
Values are concatenated with ‘|’ symbol. Need to be spitted into different row.

For example:

Input table (table_a):
___________________________
COLUMN_1
___________________________
5312|9316|F645
---|---|---|1568
F101
1989|1929|---|0028|2166


Desired output

___________________________
COLUMN_1
___________________________
5312
9316
F645
---
---
---
1568
F101
1989
1929
---
0028
2166



is there anyway to do it in sql?

Regards,
Naveen

[Updated on: Mon, 03 December 2007 16:25]

Report message to a moderator

Re: sql query [message #285189 is a reply to message #285167] Mon, 03 December 2007 20:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you need it to BE on a different row, or just LOOK like it is on a different row?

If it just needs to look like it is on a different row, you could replace '|' with a linefeed:

SELECT replace(column_1, '|', chr(10))
FROM table_a


If you really need to split it into separate rows - ie. Query finds 4 rows in the table but returns (in your case) 13 rows, then it's a bit more complex.

It can be done with CONNECT BY or MODEL clauses, but these are a misuse of the technology. The better way is to create a Nested Table Type and cast the concatenated values to a Nested Table.

SQL> CREATE OR REPLACE TYPE vc2arr AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION convert_vc2arr (
  2          c IN CLOB
  3  ,       s IN VARCHAR2 := ','
  4  ) RETURN vc2arr IS
  5          r vc2arr;
  6          i INTEGER;
  7          letter CHAR(1);
  8          word VARCHAR2(4000) := NULL;
  9  BEGIN
 10          r := vc2arr();
 11
 12          FOR i IN 1 .. length(c)+length(s) LOOP
 13           letter := substr(c||s, i, 1);
 14
 15           IF letter = s THEN
 16            r.EXTEND;
 17            r(r.LAST) := word;
 18            word := NULL;
 19           ELSE
 20            word := word || letter;
 21           END IF;
 22          END LOOP;
 23
 24          RETURN r;
 25  END;
 26  /

Function created.

SQL>
SQL>
SQL> drop table vc2arr_test purge;

Table dropped.

SQL> create table vc2arr_test (a varchar2(4000));

Table created.

SQL>
SQL> insert into vc2arr_test values ('5312|9316|F645');

1 row created.

SQL> insert into vc2arr_test values ('---|---|---|1568');

1 row created.

SQL> insert into vc2arr_test values ('F101');

1 row created.

SQL> insert into vc2arr_test values ('1989|1929|---|0028|2166');

1 row created.

SQL>
SQL> select column_value
  2  FROM (
  3          select convert_vc2arr(a, '|') AS vc
  4          from vc2arr_test
  5  )
  6  , table(vc);

COLUMN_VALUE
--------------------------------------------------------------------------------
5312
9316
F645
---
---
---
1568
F101
1989
1929
---
0028
2166

13 rows selected.

SQL>


Ross Leishman
Re: sql query [message #285228 is a reply to message #285167] Tue, 04 December 2007 00:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or you can opt for Pipelined Table functions too..


SQL> drop type vc2arr;

Type dropped.

SQL> drop table vc2arr_test purge;

Table dropped.

SQL>  create table vc2arr_test (a varchar2(4000));

Table created.

SQL>
SQL> insert into vc2arr_test values ('5312|9316|F645');

1 row created.

SQL> insert into vc2arr_test values ('---|---|---|1568');

1 row created.

SQL> insert into vc2arr_test values ('F101');

1 row created.

SQL> insert into vc2arr_test values ('1989|1929|---|0028|2166');

1 row created.

SQL>
SQL> CREATE OR REPLACE TYPE vc2arr AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE FUNCTION  SPLIT_CHAR  RETURN VC2ARR PIPELINED
  2  AS
  3  field1 varchar2(4000) ;
  4  cnt           number(5):= 1;
  5  ret           varchar2(4000);
  6  BEGIN
  7  FOR rec IN (SELECT a FROM  VC2ARR_TEST)
  8  LOOP
  9     field1 := rec.a ||'|';
 10     WHILE (field1 IS NOT NULL)
 11     LOOP
 12       pipe row (substr(field1,1,instr(field1,'|',1)-1));
 13      field1 := substr(field1,instr(field1,'|',1)+1 );
 14     END LOOP;
 15
 16     IF rec.a IS NULL
 17     THEN
 18       dbms_output.put_line (rec.a);
 19     END IF;
 20
 21  END LOOP;
 22  END;
 23  /

Function created.

SQL> set linesize 25
SQL> select COLUMN_VALUE split from TABLE( split_char);

SPLIT
-------------------------
5312
9316
F645
---
---
---
1568
F101
1989
1929
---
0028
2166

13 rows selected.

SQL> 


Thumbs Up
Rajuvan.

[Updated on: Tue, 04 December 2007 01:24]

Report message to a moderator

Re: sql query [message #285231 is a reply to message #285167] Tue, 04 December 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a way in SQL (just a tip, can be simplified):
SQL> var myString varchar2(100)
SQL> exec :myString := '1989|1929|---|0028|2166'

PL/SQL procedure successfully completed.

SQL> select substr(:myString,
  2                instr('|'||:myString||'|', '|', 1, rn),
  3                instr('|'||:myString||'|', '|', 1, rn+1)
  4                - instr('|'||:myString||'|', '|', 1, rn) - 1) value
  5  from (select rownum rn from dual 
  6        connect by level 
  7                     <= length(:myString)-length(replace(:myString,'|',''))+1)
  8  /
VALUE
-----------------------------------------------------------------
1989
1929
---
0028
2166

5 rows selected.

Regards
Michel
Re: sql query [message #285275 is a reply to message #285231] Tue, 04 December 2007 03:25 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Michel just a small clarification required.you have concated a '|' at the leading and end of the string(myString).if you could explain it.


regards,
Re: sql query [message #285285 is a reply to message #285275] Tue, 04 December 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just to be sure instr returns a valid value for each case.
Otherwise you have to use decode/case to check if instr returns 0 (case for last item or when there is only 1 item in the string, that is no '|').

Regards
Michel
Re: sql query [message #285328 is a reply to message #285167] Tue, 04 December 2007 04:41 Go to previous messageGo to next message
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
Member
The following query will give you the desired result. But table_1 must have only 1 row. If table_1 has multiple rows then the query needs some enhancement. In that case inform me please.

--------------
select
(
case when rownum > 1 then
SUBSTR(TXT,
INSTR(TXT, '|',1,ROWNUM-1)+1,
(INSTR(txt, '|',1,ROWNUM) -INSTR(TXT, '|',1,ROWNUM-1))-1
)
ELSE
SUBSTR(TXT,1,INSTR(TXT, '|',1,1)-1)
END
) STR
from
(
SELECT COLUMN_1||'|' TXT
FROM table_1
) TABLE_1,
(
select level
from dual, table_1
connect by level <= length(COLUMN_1) - length(replace(COLUMN_1,'|',null))+1
) q
------------
Re: sql query [message #285369 is a reply to message #285328] Tue, 04 December 2007 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your post! This is unreadable and so useless.

Regards
Michel



Re: sql query [message #285453 is a reply to message #285167] Tue, 04 December 2007 13:37 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
for 10g:
SQL> with t as (select '5312|9316|F645' str from dual union all
  2             select '---|---|---|1568' from dual union all
  3             select 'F101' from dual union all
  4             select '1989|1929|---|0028|2166' from dual)
  5             --
  6             select regexp_substr(str, '[^|]+', 1, level) subs
  7               from t
  8             connect by instr(str, '|', 1, level - 1) > 0
  9                    and connect_by_root str = str
 10  /

SUBS
-----------------------
5312
9316
F645
---
---
---
1568
F101
1989
1929
---
0028
2166

13 rows selected

SQL> 
Previous Topic: database and charectorstics
Next Topic: where current of use
Goto Forum:
  


Current Time: Sat Dec 10 11:12:20 CST 2016

Total time taken to generate the page: 0.16097 seconds