OraFAQ Forum: SQL & PL/SQL » Function that will create comma separated list

Home » SQL & PL/SQL » SQL & PL/SQL » Function that will create comma separated list (8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Function that will create comma separated list [message #614529]

Thu, 22 May 2014 16:50

bobghw
Messages: 34
Registered: July 2012

Member

Hi, every now and then I get given a large list of 7 digit alpha numeric values and need to use the list in a query as a range.
To do this I need to enclose each value in single quotes separated by a comma. does anyone have a function that will do this or
one I can modify or should I use an application outside of Oracle?

Report message to a moderator

Re: Function that will create comma separated list [message #614530 is a reply to message #614529]

Thu, 22 May 2014 17:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>(8i)
Really?

use SQLLDR to load into a table called NEW_PHONES as fixed length string

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: Function that will create comma separated list [message #614545 is a reply to message #614529]

Fri, 23 May 2014 00:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select * from t order by val
  2  /
VAL
------------------------------
1234567
12345671234567
123456712345671234567

3 rows selected.

SQL> select val, line,
  2         substr(val, 1+7*line, 7) elem
  3  from t,
  4       (select rownum-1 line from (select 1 from dual group by cube(1,2,3)))
  5  where line < ceil(length(val)/7)
  6  order by val, line
  7  /
VAL                                  LINE ELEM
------------------------------ ---------- -------
1234567                                 0 1234567
12345671234567                          0 1234567
12345671234567                          1 1234567
123456712345671234567                   0 1234567
123456712345671234567                   1 1234567
123456712345671234567                   2 1234567

6 rows selected.

Report message to a moderator

Re: Function that will create comma separated list [message #614577 is a reply to message #614545]

Fri, 23 May 2014 08:07

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Did someone delete some messages from this thread, or did I eat some of the tainted food being recalled across the USA, because I don't see the connection between the responses and the question.

I'll look at some other threads, and if I exhibit the same disconnect, I'll try to get to a hospital.

Report message to a moderator

Re: Function that will create comma separated list [message #614604 is a reply to message #614577]

Fri, 23 May 2014 11:55

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

LOL! It must be pandemic, as I noted the same much earlier today (but was rather busy and didn't complain about it).

I'm not sure what BS and MC understood, but I can't imagine what has been asked.
bobghw

a large list of 7 digit alpha numeric values and need to use the list in a query as a range

How does that list look like? Is it like this:

ABC1234, 4432773, AABBCCD
or
1111111AAAAAAA1234BCD998877A
or
ABCDEFG
1234567
DEF8877
or ...?

I guess someone here would manage to "enclose each value in single quotes separated by a comma", only if we knew how the input looks like.

Report message to a moderator

Previous Topic:	Subquery
Next Topic:	Split column data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:08:38 CDT 2024