Extract certain pattern with Oracle SQL

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

Answers


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.


Need Your Help

Check existence of a record before returning resultset in LINQ to SQL

c# .net linq linq-to-sql

I'm looking for a simple solution to replace my standardized junk way of validating whether a record exists before attempting to retrieve the data. Currently, whenever one of my methods are called...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.