Home » SQL & PL/SQL » SQL & PL/SQL » extracting multiple Numbers from String Column.. (Oracle 10i)
extracting multiple Numbers from String Column.. [message #380812] Tue, 13 January 2009 07:50 Go to next message
morrisp6
Messages: 5
Registered: January 2009
Location: Manchester, England
Junior Member
Hi there, i am stuck on this problem, and any assistance or ideas would be much appreciated.

I have a table of data, which contains a column of text data. Within this text, are some key numbers that i want to be able to extract, there could be 1, or up to 6 of them, and they won't always be in the same place, or same sequence, but they do have a pattern, i am stuck though on how to get all of these numbers out, ideally into a comma separated column.

The data looks like this:

"Info : 1234 : Pro - Start .~ Error : 592 : Order Committed .~ N/A"

The bits i need are the "1234", and "592", and these always appears in the pattern ": xxx : " or ": xxxx : " depending on whether a 3 or 4 digits number.

I have tried a few different approaches, the last being to use a reg_exp to extract the sub-string, along the lines of:

REGEXP_SUBSTR(L.error_code,':digit:{3}') as xyz but in the example this only returns 123...

Any ideas, or methods people can think of would be much appreciated, i can't see the wood for the trees as they say at the moment!

Thanks
Paul
Re: extracting multiple Numbers from String Column.. [message #380816 is a reply to message #380812] Tue, 13 January 2009 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as 
  2    (select 'Info : 1234 : Pro - Start .~ Error : 592 : Order Committed .~ N/A' code from dual)
  3  select REGEXP_SUBSTR(code, '[[:digit:]]{3,4}') c1,
  4         REGEXP_SUBSTR(code, '[[:digit:]]{3,4}',1,2) c2
  5  from data
  6  /
C1   C2
---- ---
1234 592

1 row selected.

Regards
Michel
Re: extracting multiple Numbers from String Column.. [message #380818 is a reply to message #380812] Tue, 13 January 2009 08:06 Go to previous messageGo to next message
morrisp6
Messages: 5
Registered: January 2009
Location: Manchester, England
Junior Member
Thanks Michel, I had a brainwave just looking over the post, and came up with similar tatic.

REGEXP_SUBSTR(L.error_code,':digit:+',1,1) || ',' ||
REGEXP_SUBSTR(L.error_code,':digit:+',1,2) || ',' ||
REGEXP_SUBSTR(L.error_code,':digit:+',1,3) || ',' ||
REGEXP_SUBSTR(L.error_code,':digit:+',1,4) || ',' ||
REGEXP_SUBSTR(L.error_code,':digit:+',1,5) || ',' ||
REGEXP_SUBSTR(L.error_code,':digit:+',1,6)
as abc


I like the idea of specifying that it is 3 or 4 in length though, that should weed out any odd numbers that appear in the text!

Thanks again
Paul
Re: extracting multiple Numbers from String Column.. [message #380822 is a reply to message #380818] Tue, 13 January 2009 08:29 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can have digits outside error code, you can use:
SQL> with data as 
  2    (select 'Info : 1234 : Pro - Start .~ Error : 592 : Order Committed .~ N/A' code from dual)
  3  select rtrim(ltrim(REGEXP_SUBSTR(code, ': *[[:digit:]]{3,4} *:'),': '),': ') c1,
  4         rtrim(ltrim(REGEXP_SUBSTR(code, ': *[[:digit:]]{3,4} *:',1,2),': '),': ') c2
  5  from data
  6  /
C1   C2
---- ---
1234 592

1 row selected.

Regards
Michel

[Updated on: Tue, 13 January 2009 08:31]

Report message to a moderator

Previous Topic: SQL Query
Next Topic: How many "SUNDAY" in a Month
Goto Forum:
  


Current Time: Sat Dec 10 20:18:12 CST 2016

Total time taken to generate the page: 0.14798 seconds