Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_SUBSTR (10g)
REGEXP_SUBSTR [message #357822] Thu, 06 November 2008 15:45 Go to next message
theladyd
Messages: 13
Registered: September 2008
Junior Member
I have a column of numbers from 1 to 10 digits in length.
1
22
333
4444
55555
666666, etc.

I want to extract the last 2 digits of each number. I know how to extract from left to right REGEXP_SUBSTR(EMPLOYEE_NBR,'..', 5), how do I extract from right to left?

Thanks
Re: REGEXP_SUBSTR [message #357823 is a reply to message #357822] Thu, 06 November 2008 15:49 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why regular expressions? How about simple SUBSTR?
SQL> WITH test AS
  2   (SELECT 1 col FROM dual
  3    UNION SELECT 21 FROM dual
  4    UNION SELECT 312 FROM dual
  5    UNION SELECT 4234 FROM dual
  6    UNION SELECT 56543 FROM dual
  7   )
  8  SELECT col, SUBSTR(col, -2) FROM test;

       COL SU
---------- --
         1
        21 21
       312 12
      4234 34
     56543 43

SQL>
Re: REGEXP_SUBSTR [message #357824 is a reply to message #357822] Thu, 06 November 2008 15:54 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
I think this may help...
select SUBSTR(employee_nbr,-2)
from <table_name>;
Re: REGEXP_SUBSTR [message #357825 is a reply to message #357823] Thu, 06 November 2008 15:55 Go to previous message
theladyd
Messages: 13
Registered: September 2008
Junior Member
Thanks, I was thinking that regular substr did not work in oracle. Thanks for the quick response.
Previous Topic: ORA-00997: illegal use of LONG datatype
Next Topic: Newbie questions about regular expressions.
Goto Forum:
  


Current Time: Sun Dec 04 02:18:15 CST 2016

Total time taken to generate the page: 0.18600 seconds