Home » SQL & PL/SQL » SQL & PL/SQL » regexp replace (Oracle 11g, Win 7)
regexp replace [message #652608] Tue, 14 June 2016 13:29 Go to next message
rajivn786
Messages: 153
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.
Re: regexp replace [message #652609 is a reply to message #652608] Tue, 14 June 2016 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
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 Go to previous messageGo to next message
rajivn786
Messages: 153
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.
Re: regexp replace [message #652611 is a reply to message #652610] Tue, 14 June 2016 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
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.

Re: regexp replace [message #652612 is a reply to message #652611] Tue, 14 June 2016 14:23 Go to previous messageGo to next message
rajivn786
Messages: 153
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]
Re: regexp replace [message #652613 is a reply to message #652612] Tue, 14 June 2016 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
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.
Re: regexp replace [message #652614 is a reply to message #652613] Tue, 14 June 2016 14:37 Go to previous messageGo to next message
rajivn786
Messages: 153
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.
Re: regexp replace [message #652615 is a reply to message #652614] Tue, 14 June 2016 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
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.
Re: regexp replace [message #652616 is a reply to message #652615] Tue, 14 June 2016 14:50 Go to previous messageGo to next message
rajivn786
Messages: 153
Registered: January 2010
Senior Member
Thanks.. So far so good.
I will let you know if there are further difficulties.
Re: regexp replace [message #652619 is a reply to message #652616] Tue, 14 June 2016 15:55 Go to previous message
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:
  


Current Time: Tue Jul 27 20:15:25 CDT 2021