Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE function usage (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
REGEXP_REPLACE function usage [message #329969] Fri, 27 June 2008 03:03 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I have data something like
1324#1
14567#2
12345#12
1234#1

and this value is in single record. I want to replace the #, characters in between and CHR(10) with space. I am not sure how to get it done by REGEXP_REPLACE function.

SQL> create table t(col1 varchar2(100));

Table created.

SQL> INSERT INTO t VALUES('1324#1'||CHR(10)||'14567#2'|| CHR(10)||'12345#12'|| CHR(10) || '1234#1');


SQL> select * from t;

COL1
--------------------------------------------------------------------------------
1324#1
14567#2
12345#12
1234#1


The output I need is

SQL> select * from t;

1324 14567 12345 1234
Re: REGEXP_REPLACE function usage [message #329975 is a reply to message #329969] Fri, 27 June 2008 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using REGEXP_REPLACE when TRANSLATE is the correct one?

Regards
Michel
Re: REGEXP_REPLACE function usage [message #329979 is a reply to message #329969] Fri, 27 June 2008 03:19 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yes I tried with TRANSLATE as well, but couldn't get the desired result. Sad
Re: REGEXP_REPLACE function usage [message #329980 is a reply to message #329979] Fri, 27 June 2008 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't see a way to replace a variable string of characters using TRANSLATE.

You asked for:
 I want to replace the #, characters in between and CHR(10) with space.


SQL> select regexp_replace(col1,'#.*'||chr(10),' ') from t;

REGEXP_REPLACE(COL1,'#.*'||CHR(10),'')
--------------------------------------------------------------------------------
1324 14567 12345 1234#1


However, to get the results you posted from the data you posted is a little more complex, as we need to optionally append a chr(10) to the end of COL1 if is doesn't have one:
SQL> select regexp_replace(case when substr(col1,-1) = chr(10) then col1 else col1||chr(10) end
  2                       ,'#.*'||chr(10),' ') from t;

REGEXP_REPLACE(CASEWHENSUBSTR(COL1,-1)=CHR(10)THENCOL1ELSECOL1||CHR(10)END,'#.*'
--------------------------------------------------------------------------------
1324 14567 12345 1234
Re: REGEXP_REPLACE function usage [message #329981 is a reply to message #329969] Fri, 27 June 2008 03:29 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks JRowbottom. That what exactly I was looking for Surprised

I think I need to better understand the REGEX function.

Re: REGEXP_REPLACE function usage [message #329986 is a reply to message #329979] Fri, 27 June 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
himang wrote on Fri, 27 June 2008 10:19
Yes I tried with TRANSLATE as well, but couldn't get the desired result. Sad

I just misunderstood your requirements.

Regards
Michel

Re: REGEXP_REPLACE function usage [message #329987 is a reply to message #329981] Fri, 27 June 2008 03:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's the documentation on Oracle's Regexp implementation, and how to build a regular expression.

Re: REGEXP_REPLACE function usage [message #329989 is a reply to message #329986] Fri, 27 June 2008 03:44 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Fri, 27 June 2008 14:08
himang wrote on Fri, 27 June 2008 10:19
Yes I tried with TRANSLATE as well, but couldn't get the desired result. Sad

I just misunderstood your requirements.

Regards
Michel



It happens with GURUS Smile

Quote:
Here's the documentation on Oracle's Regexp implementation, and how to build a regular expression.
Thanks
Re: REGEXP_REPLACE function usage [message #330061 is a reply to message #329989] Fri, 27 June 2008 08:39 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just another option:
SQL> select * from t;

COL1
------------
1324#1
14567#2
12345#12
1234#1


SQL> select trim(regexp_replace(col1, '#.*('||chr(10)||'|$)', ' ')) new_col1 from t;

NEW_COL1
-------------------------
1324 14567 12345 1234
Previous Topic: DATE ISSUES
Next Topic: calling values from a parameter
Goto Forum:
  


Current Time: Thu Dec 08 12:25:04 CST 2016

Total time taken to generate the page: 0.13118 seconds