Extracting multiple numbers from string variable in Oracle

Question

I have a varchar variable with contents like this: '123,12,7654321,9998...'. I work in Oracle 10gR2. How can I achieve the following output format?:

------
123
12
7654321
9998
...
------

Thanks!

rs. · Accepted Answer · 2013-01-30 19:07:10Z

1

Try this

SELECT EXTRACTVALUE(COLUMN_VALUE,'text()') VALS 
FROM XMLTABLE('123,12,7654321,9998');

or

SELECT COLUMN_VALUE VALS FROM XMLTABLE('123,12,7654321,9998');

answered Jan 30, 2013 at 19:07

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-01-30 19:10:16Z

0

Here is a way using connect:

with str as (select '123,12,7654321,9998' as val from dual)
select cast(regexp_substr(val,'[^,]+', 1, level) as int)
from str
connect by cast(regexp_substr(val, '[^,]+', 1, level) as int) is not null;

I'll admit that I adapted it from this article. What you are trying to do is commonly put into a function called "split".

answered Jan 30, 2013 at 19:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Sergey Over a year ago

Hm, thank you, Gordon Linoff! I'll try to apply your advice..Unfortunately, I don't know about connect by cast...It's time for learning!)

Swapna Mohan · Accepted Answer · 2013-01-30 19:25:53Z

0

Try this :

/* Formatted on 1/30/2013 1:24:07 PM (QP5 v5.227.12220.39724) */
WITH val AS (SELECT '123,12,7654321,9998' txt FROM DUAL)
SELECT REGEXP_SUBSTR (txt,
              '[0-9]+|[a-z]+|[A-Z]+',
              1,
              lvl)
  FROM (SELECT txt,
           LEVEL lvl
      FROM val
    CONNECT BY LEVEL <=   LENGTH (txt)
                - LENGTH (REPLACE (txt,
                           ','))
                + 1)

answered Jan 30, 2013 at 19:25

Swapna Mohan

1751 silver badge13 bronze badges

2 Comments

Sergey Over a year ago

Thank you, Swapna Mohan! It works too!) Can you advise any books about regular expression or all I need is reading docs.oracle.com?

Swapna Mohan Over a year ago

I am sorry, I don't know of any books on regular expressions. docs.oracle.com is all i used for that and ofcourse code from projects that I worked on.

Collectives™ on Stack Overflow

Extracting multiple numbers from string variable in Oracle

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related