Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression Query (REGEXP_SUBSTR) (Oracle 10.2.0.1.0, Windows XP Pro)
Regular Expression Query (REGEXP_SUBSTR) [message #359080] Thu, 13 November 2008 12:29 Go to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

I am learning about regular expressions and I am stuck with a few queries regarding REGEXP_SUBSTR. This happened when I tried the following query:
SQL> SELECT REGEXP_SUBSTR ('456-435-3335',
  2                        '([ [:digit:] ]{3})-([ [:digit:] ]{3})-([ [:digit:] ]{4})'
  3                       )
  4    FROM DUAL;

REGEXP_SUBST
------------
456-435-3335

1 row selected.

(There is no space between '[ [' and '] ]'. I tried ebrian's way but it didn't help. Maybe I tried it wrong.
To be honest, the query was taken from Oracle Complete Reference book).

So I made myself a requirement out of curiosity to understand REGEXP_SUBSTR.

I hope the following WITH Clause can act as a test case rather than create table statements:
WITH test_tab AS
     (SELECT '34546-45678-34568' col_1
        FROM DUAL
      UNION
      SELECT '345-5555-6785' col_1
        FROM DUAL
      UNION
      SELECT '4567-3345-456' col_1
        FROM DUAL)
SELECT col_1
  FROM test_tab


And my SQL*Plus Session with the desired results is as follows:
SQL> WITH test_tab AS
  2       (SELECT '34546-45678-34568' col_1
  3          FROM DUAL
  4        UNION
  5        SELECT '345-5555-6785' col_1
  6          FROM DUAL
  7        UNION
  8        SELECT '4567-3345-456' col_1
  9          FROM DUAL)
 10  SELECT col_1
 11    FROM test_tab;

COL_1
-----------------
345-5555-6785
34546-45678-34568
4567-3345-456

3 rows selected.

-- DESIRED RESULT --

COL_1             EXTRACTED
----------------- -----------------
345-5555-6785     345-555-678
34546-45678-34568 345-456-345
4567-3345-456     456-334-456



I want to extract the first three digits of col_1 which are seperated by hyphen('-'). I am stuck finding the appropriate match pattern. Can anyone guide me how to achieve this with REGEXP_SUBSTR?

Thanks,
Joice http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

(Please post any references which might be helpful other than Using Regular Expressions in Oracle Database. I am going through this one right now.)
Re: Regular Expression Query (REGEXP_SUBSTR) [message #359085 is a reply to message #359080] Thu, 13 November 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe (surely) not the best way to do it but it expresses exactly what you said:
SQL> WITH test_tab AS
  2       (SELECT '34546-45678-34568' col_1
  3          FROM DUAL
  4        UNION
  5        SELECT '345-5555-6785' col_1
  6          FROM DUAL
  7        UNION
  8        SELECT '4567-3345-456' col_1
  9          FROM DUAL)
 10  SELECT col_1,
 11         regexp_replace(col_1,
 12                        '(^[[:digit:]]{3})[^-]*-([[:digit:]]{3})[^-]*-([[:digit:]]{3}).*$',
 13                        '\1-\2-\3') extracted
 14  from test_tab
 15  /
COL_1                EXTRACTED
-------------------- --------------------
345-5555-6785        345-555-678
34546-45678-34568    345-456-345
4567-3345-456        456-334-456

3 rows selected.

( I replaced the second [ of each expression by &91#; )

Regards
Michel

[Updated on: Thu, 13 November 2008 12:52]

Report message to a moderator

Re: Regular Expression Query (REGEXP_SUBSTR) [message #359094 is a reply to message #359080] Thu, 13 November 2008 13:10 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thanks Michel for a quick response.

Hmm... So it can be achieved only through REGEXP_REPLACE (Still have to beat that one down my head) as opposed to my post, is it?

joicejohn wrote on Thu, 13 November 2008 23:59

how to achieve this with REGEXP_SUBSTR?



I think my "imaginary" requirement was for a wrong function then? http://img2.mysmiley.net/imgs/smile/ashamed/ashamed0003.gif

Appreciated your help.

Regards,
Jo
Re: Regular Expression Query (REGEXP_SUBSTR) [message #359100 is a reply to message #359094] Thu, 13 November 2008 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In one expression I don't see how you can do it but...
SQL> WITH test_tab AS
  2       (SELECT '34546-45678-34568' col_1
  3          FROM DUAL
  4        UNION
  5        SELECT '345-5555-6785' col_1
  6          FROM DUAL
  7        UNION
  8        SELECT '4567-3345-456' col_1
  9          FROM DUAL)
 10  SELECT col_1,
 11         regexp_substr(col_1,'((^|-)[[:digit:]]{3})',1,1)||
 12         regexp_substr(col_1,'((^|-)[[:digit:]]{3})',1,2)||
 13         regexp_substr(col_1,'((^|-)[[:digit:]]{3})',1,3)       
 14           extracted
 15  from test_tab
 16  /
COL_1             EXTRACTED
----------------- -----------------------
345-5555-6785     345-555-678
34546-45678-34568 345-456-345
4567-3345-456     456-334-456

3 rows selected.

Regards
Michel

[Updated on: Thu, 13 November 2008 13:30]

Report message to a moderator

Re: Regular Expression Query (REGEXP_SUBSTR) [message #359105 is a reply to message #359100] Thu, 13 November 2008 13:48 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

That was a neat one. http://img2.mysmiley.net/imgs/smile/cool/cool0020.gif
I didn't had the idea of splitting the data and using three REGEXP_SUBSTR. http://img2.mysmiley.net/imgs/smile/mad/mad0235.gif

Thank you once again...

Thanks & Regards,
Jo
Previous Topic: Simple Select Statement
Next Topic: modify sql to select only non-alphabetic emplid's
Goto Forum:
  


Current Time: Sun Dec 11 02:39:38 CST 2016

Total time taken to generate the page: 0.07834 seconds