Home » SQL & PL/SQL » SQL & PL/SQL » sql equivalent of dbms_utility.comma_to_table ? (10g)
sql equivalent of dbms_utility.comma_to_table ? [message #421819] Thu, 10 September 2009 17:42 Go to next message
mv_paul
Messages: 4
Registered: September 2009
Junior Member
Hi all,

I have a field that has as value comma separated numbers, like '1234,1235,1236'. These are ids that each map to an id column.

Is there a way in sql (not pl/sql) to translate the invalid statement below into a valid sql one?

Select id, foo from table where id in (select list_field from T)

Some sql equivalent of dbms_utility.comma_to_table ?

Thanks.
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421820 is a reply to message #421819] Thu, 10 September 2009 17:51 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And here we have the famous question about varying in-list again that gets posted almost every day.
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421832 is a reply to message #421819] Thu, 10 September 2009 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PLease search BEFORE posting, this is a FAQ and answer many times not so far than 4 hours before your question: http://www.orafaq.com/forum/m/421812/102589/#msg_421812

Regards
Michel
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421895 is a reply to message #421819] Fri, 11 September 2009 08:47 Go to previous messageGo to next message
mv_paul
Messages: 4
Registered: September 2009
Junior Member
Thank you for your responses. But, as I mentioned, I was asking if a solution where pl/sql is not involved is possible, and how would that look like.

Is it possible?
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421896 is a reply to message #421895] Fri, 11 September 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only you don't search but you are unable to read the links we post.

Regards
Michel
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421897 is a reply to message #421819] Fri, 11 September 2009 09:14 Go to previous messageGo to next message
mv_paul
Messages: 4
Registered: September 2009
Junior Member
You're too kind. Actually I'm able to "read the links" (whatever "reading a link" might mean). The first link points to an asktom article involving pl/sql. I trust you've read that?

The other link provided has nothing to do with my question.

Thank you very much.
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421903 is a reply to message #421897] Fri, 11 September 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The first link points to an asktom article involving pl/sql.

This is not the only solution posted on AskTom for the subject (but maybe you are unable to use the search feature).
And yes I have this thread and many other in which I collaborated.
And you didn't follow (as you don't like "read") the other link.

Regards
Michel

Re: sql equivalent of dbms_utility.comma_to_table ? [message #421965 is a reply to message #421819] Sat, 12 September 2009 10:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Acutally you were too lazy to read the full article. Yes examples are posted in plsql but they contain at least one sql solution that will work for you. Sometimes we expect you to do a little work on your own.

About 3/4 of the way down the posting you will find this:

Followup   December 29, 2005 - 12pm US/Eastern:

ops$tkyte@ORA9IR2> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_string varchar2(100) := 'SCOTT, SYS';
  3  begin
  4      insert into t
  5      select *
  6        from all_users
  7       where username in
  8       ( select  trim(
  9                 substr (txt,
 10                 instr (txt, ',', 1, level  ) + 1,
 11                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, 
                                                                 level) -1 ))
 12           from (select ','||l_string||',' txt from dual)
 13        connect by level <= 
                       length(l_string)-length(replace(l_string,',',''))+1
 14       );
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from t;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SCOTT                                  60 06-DEC-03
SYS                                     0 06-DEC-03

Is another way in 9ir2 and above to do this. 


Is this a plsql sql example, yes. Does it contain a sql solution for you, yes.

This is the "connect by level" row generation version of your "delimited string parsing" need.

If the plsql is confusing you or you do not believe this is a sql only solution then the next response down in the asktomhome article is this:

Followup   January 13, 2006 - 11am US/Eastern:

that works, or you can:


ops$tkyte@ORA9IR2> variable txt varchar2(25)
ops$tkyte@ORA9IR2> exec :txt := 'a,bb,ccc,d,e,f';

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> with data
  2  as
  3  (
  4  select substr (txt,
  5                 instr (txt, ',', 1, level  ) + 1,
  6                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  7           as token
  8    from (select ','||:txt||',' txt from dual)
  9  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 10  )
 11  select * from data;

TOKEN
----------------------------------
a
bb
ccc
d
e
f

6 rows selected.


This is exactly what you wanted is it not. In one of the articles Michele gave you. Michele is rarely wrong.

Good luck, Kevin
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421982 is a reply to message #421819] Sat, 12 September 2009 11:32 Go to previous messageGo to next message
mv_paul
Messages: 4
Registered: September 2009
Junior Member
I should have read that page more carefully. Thanks all for taking the time to point me in the right direction.
Re: sql equivalent of dbms_utility.comma_to_table ? [message #421990 is a reply to message #421819] Sat, 12 September 2009 16:56 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
No problem. In all fairness, when I was first looking for this solution a while back I too ran across this page and was not sure if I wanted to read it all (hehe).

Good luck, Kevin
Previous Topic: How to avoid nulls with greatest function?
Next Topic: unix time insertion into db?
Goto Forum:
  


Current Time: Sat Sep 24 21:26:35 CDT 2016

Total time taken to generate the page: 0.07956 seconds