Extract certain pattern with Oracle SQL

Question

I have a string like

with xx as (
select 'id9' idno,'untest X456789,W357987 and Q321089 cont group' test from dual)
select * from xx

There are some thosand rows like the following

       IDNO |                             TEST
      +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        id9 | untest X456789,W357987 and Q321089 cont group

I want to extract words that begin with a letter followed by 6 digits. Also, there should be a comma between them (because later I will place them to multiple rows)

Resulting Table:

        IDNO |                TEST
      +++++++++++++++++++++++++++++++++++++++++
        id9 | X456789,X321678,W357987,Q321089

I have tried regexp_replace, but could not reach a solution.

select idno, REGEXP_replace( test,'([^[A-Z]{1}[:digit:]{6},?])') AS test from xx

It is an OLAP environment which is denormalized purposefully. (less joins, cleaner code). Above table was received from a third party institution, and unfortunately we did not have the chance to format it according to our needs. — Mehmet Balioglu
– Mehmet Balioglu, Commented Aug 2, 2013 at 12:14
That's ok as long as it's done on purpose and not by poor understanding of normalization. Good luck! :) — STT LCU
– STT LCU, Commented Aug 2, 2013 at 12:18

Gordon Linoff · Accepted Answer · 2013-08-02 12:59:35Z

4

The following does what you want for your original string:

with xx as (
select 'id9' idno,'untest X456789,W357987 and Q321089 cont group' test from dual
)
select idno,
       REGEXP_replace(test,
                      '([A-Z]{1}[0-9]{6}[ ,]?)|(.)', '\1'
                     ) AS test
from xx;

Getting the second space to be a comma . . . you can use a regular replace:

with xx as (
select 'id9' idno,'untest X456789,W357987 and Q321089 cont group' test from dual
)
select idno,
       replace(REGEXP_replace(test,
                              '([A-Z]{1}[0-9]{6}[ ,]?)|(.)', '\1'
                             ),
               ' ', ',') AS test
from xx;

The SQL Fiddle is here.

edited Aug 2, 2013 at 12:59

answered Aug 2, 2013 at 12:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Mehmet Balioglu Over a year ago

Mr. Linoff, first query did not make a change in the original string. Second one just placed commas between all the words.

Gordon Linoff Over a year ago

@bonsvr . . . I apologize for that. I had it working in SQL Fiddle and apparently copied the wrong queries back to the answer. Fixed.

Mehmet Balioglu Over a year ago

Thank you very much. Learned much from your book BTW.

Collectives™ on Stack Overflow

Extract certain pattern with Oracle SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related