OraFAQ Forum: SQL & PL/SQL » regexp replace

Home » SQL & PL/SQL » SQL & PL/SQL » regexp replace (Oracle 11g, Win 7)

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

regexp replace [message #652608]

Tue, 14 June 2016 13:29

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Hi,

I have quick doubt regarding regexp which I am poor at.

Ex :


 Input String : Contact the doctor Dr. Remo Henry  at 646-133-7089 on 02/02/2016.

 Output : Contact the doctor Dr. Fname Lname at phone on date.

 Select regexp_replace ('Contact the doctor Dr. Remo Henry  at 646-133-7089 on 02/02/2016.', ) from dual;

From the above, wherever there is a name/phone/date in the string, any first/last name of doctor should be replaced by string "Fname Lname" , phone number should be replaced by string "phone" and date should be replaced by string "date".

Thanks.

Report message to a moderator

Re: regexp replace [message #652609 is a reply to message #652608]

Tue, 14 June 2016 14:01

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How do we know what is a first name, what is a last name and what is a phone number?

[Updated on: Tue, 14 June 2016 14:01]

Report message to a moderator

Re: regexp replace [message #652610 is a reply to message #652609]

Tue, 14 June 2016 14:08

rajivn786
Messages: 161
Registered: January 2010

Senior Member

We don't need to know...All the above process it to just mask the data ...It doesn;t matter whether it's first name or last name.
For the phone number as long as the string has the format of nnn-nnn-nnnn or (nnn)-nnn-nnnn it needs to be replace by string "phone". (as most of the ph no's are in this format)

The only reason is the end user shouldn't know about the doctor or phone details.

Report message to a moderator

Re: regexp replace [message #652611 is a reply to message #652610]

Tue, 14 June 2016 14:12

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In your example, how Oracle knows the "Dr" is not a first or last name?
How does it know that "02/02/2016" is not a phone number?
How does it know that first and last names are "Remo" and "Henry" and not "Contact" and "the"?
For it, this is just a string made of equal characters, there is no semantic attached to these ones.

Report message to a moderator

Re: regexp replace [message #652612 is a reply to message #652611]

Tue, 14 June 2016 14:23

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Thanks for the questions.
We need to go by formats..

For the names, as per our data (being a hospital) as long as there are 2 words followed by "Dr." it should be replaced by Dr. Fname Lname. Even if it's represented as Dr. Contact The, it should be replaced by Dr. Fname Lname

Similarly as long as the data format is nnn-nnn-nnnn or (nnn)-nnn-nnnn it should be replaced by text "phone"

[code]

Report message to a moderator

Re: regexp replace [message #652613 is a reply to message #652612]

Tue, 14 June 2016 14:28

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So assuming that first name and last name is ALWAYS preceded by "Dr.":

SQL> select regexp_replace(regexp_replace(
  2           'Contact the doctor Dr. Remo Henry  at 646-133-7089 on 02/02/2016.',
  3           'Dr.\s+\w+\s+\w+', 'Dr. Fname Lname'),
  4           '(?\d{3})?-\d{3}-\d{4}', 'phone') res
  5  from dual
  6  /

RES
-----------------------------------------------------------
Contact the doctor Dr. Fname Lname  at phone on 02/02/2016.

Report message to a moderator

Re: regexp replace [message #652614 is a reply to message #652613]

Tue, 14 June 2016 14:37

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Thanks. Even the date "02/02/2016" needs to be replaced by "Date"

Anything in the format of nn/nn/nnnn shoule be formatted as text "date".

The output should be Contact the doctor Dr. Fname Lname at phone on date.

Report message to a moderator

Re: regexp replace [message #652615 is a reply to message #652614]

Tue, 14 June 2016 14:41

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If the date is always in this format:

SQL> select regexp_replace(regexp_replace(regexp_replace(
  2           'Contact the doctor Dr. Remo Henry  at 646-133-7089 on 02/02/2016.',
  3           'Dr.\s+\w+\s+\w+', 'Dr. Fname Lname'),
  4           '(?\d{3})?-\d{3}-\d{4}', 'phone'),
  5           '\d{2}/\d{2}/\d{4}','date') res
  6  from dual
  7  /

RES
-----------------------------------------------------
Contact the doctor Dr. Fname Lname  at phone on date.

Report message to a moderator

Re: regexp replace [message #652616 is a reply to message #652615]

Tue, 14 June 2016 14:50

rajivn786
Messages: 161
Registered: January 2010

Senior Member

Thanks.. So far so good.
I will let you know if there are further difficulties.

Report message to a moderator

Re: regexp replace [message #652619 is a reply to message #652616]

Tue, 14 June 2016 15:55

Bill B
Messages: 1971
Registered: December 2004

Senior Member

By the way, there is a very nice and free application to learn regular expressions and to build the expression strings. I used it to learn regular expressions. it is called expresso.

http://www.ultrapico.com/Expresso.htm

[Updated on: Tue, 14 June 2016 16:02]

Report message to a moderator

Previous Topic:	External Table and Blank spaces
Next Topic:	What is wrong with this sql code?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:28:58 CDT 2024