CODEWITHSUNDEEP
regexoracle-database
Rodrigo Zem
Rodrigo Zem

Reputation: 269

How to return all results using REGEXP_SUBSTR?

I need four results to be returned, and only three are showed. How could I do this?

Query:

SELECT REGEXP_SUBSTR(lines, '[0-9]{1,3}', 1, 1,'m')
from 
(
      SELECT '111 - first line' lines FROM dual
      UNION
      SELECT '222 - second line'      FROM dual
      UNION
      SELECT '333 - third  line
              444 - fourth line' FROM dual
)

It's returning this:

111
222
333

I want this:

111
222
333
444

Is this possible?

Upvotes: 0

Views: 246

Answers (1)

DARK_A
DARK_A

Reputation: 575

At first lets make some data:

CREATE TABLE some_table (some_data VARCHAR2(200))
/
INSERT INTO some_table VALUES ('111 - first line')
/
INSERT INTO some_table VALUES ('222 - second line')
/
INSERT INTO some_table VALUES ('333 - third  line
444 - fourth line')
/
INSERT INTO some_table VALUES ('555 - fifth  line
                         666 - some ugly line')
/
INSERT INTO some_table VALUES ('123 - meh meh
                         321 - one more
                         678 - and more
                         986 - and more :)')
/

Then let's make a query:

    SELECT DISTINCT TRIM(REGEXP_SUBSTR(some_data,'[^'||CHR(10)||']+', 1, level)) 
      FROM some_table
   CONNECT BY REGEXP_SUBSTR(some_data, '[^'||CHR(10)||']+', 1, level) IS NOT NULL;

You can easy implement your query to this. I was a little bit lazy to think, so i put a DISTINCT to remove repeating ROWS. In proper code you should not use it.

Your data will look like this:

333 - third  line
678 - and more
986 - and more :)
321 - one more
555 - fifth  line
444 - fourth line
666 - some ugly line
222 - second line
123 - meh meh
111 - first line

Upvotes: 2

Related Questions