OraFAQ Forum: SQL & PL/SQL » REGEXP

Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE Help NEEDED.... (Oracle, 11.2.3, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

REGEXP_REPLACE Help NEEDED.... [message #635080]

Thu, 19 March 2015 15:19

eastbay_oracle
Messages: 4
Registered: October 2010
Location: USA

Junior Member

Hi..

I need some help pretty badly.. I have been working on this problem for days and cannot find a solution. I need to replace two numbers with two letters in the beginning of a value. The example I have is this is the value:

021111116 and I need to replace the 02 with SF and delete the last number 6.

I have been experimenting with regexp_replace and substr. I cannot get it to work...

I would greatly appreciate any help offered!

Thanks...

EBO

Report message to a moderator

Re: REGEXP_REPLACE Help NEEDED.... [message #635081 is a reply to message #635080]

Thu, 19 March 2015 15:37

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why regular expressions? SUBSTR does it quite well:

SQL> with test as
  2    (select '021111116' col from dual)
  3  select 'SF' || substr(col, 3, length(col) - 3) result
  4  from test;

RESULT
--------
SF111111

SQL>

Report message to a moderator

Re: REGEXP_REPLACE Help NEEDED.... [message #635083 is a reply to message #635081]

Thu, 19 March 2015 16:29

eastbay_oracle
Messages: 4
Registered: October 2010
Location: USA

Junior Member

Thanks! It works, but the when I use it to update my table it errors out saying returns too many rows...

I tried to narrow it down with a where clause, but still errors.

Report message to a moderator

Re: REGEXP_REPLACE Help NEEDED.... [message #635084 is a reply to message #635083]

Thu, 19 March 2015 16:59

eastbay_oracle
Messages: 4
Registered: October 2010
Location: USA

Junior Member

I got the statement to work by combining it with REPLACE..

UPDATE SCHEMAOWNER.TABLENAME
SET employeeID = REPLACE(employeeID,employeeID,'SF'||substr(employeeID, 3, length(employeeID) - 3);

Thanks for your help!

EBO

Report message to a moderator

Previous Topic:	EXISTS SQL QUERY
Next Topic:	synonyms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 00:39:01 CDT 2026