Home » SQL & PL/SQL » SQL & PL/SQL » to extract data (oracle 10G+windows xp)
to extract data [message #314529] Thu, 17 April 2008 01:33 Go to next message
maham_158
Messages: 5
Registered: January 2008
Junior Member
Hi,
I have to use regexp, my task is to replace 'AND' to '&'
and ADD ST BEFORE '&' AND omit 'S'. I am sending the given data and required output to make it easy to understand. I am sending my query as well which unfortunately does not work. Thanks in advance

CNR PYRMONT AND BUNN STS

CNR PYRMONT ST & BUNN ST

INput Data
______________________________

CNR PYRMONT AND BUNN STS
CNR SHIELDS AND SHERIDAN STS

Required output
_____________________________

CNR PYRMONT ST & BUNN ST
CNR SHIELDS ST & SHERIDAN ST
____________________________
MY QUERY

select A,
REGEXP_SUBSTR(A,'^[^AND]+')||' ST & ' || REGEXP_REPLACE(REGEXP_SUBSTR(A,'[^AND]+$'),'.{1}$')
from TESTA


Best Regards,
Maham
Re: to extract data [message #314531 is a reply to message #314529] Thu, 17 April 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to use regexp,

Why? Use replace. Or is this a homework?
In this case and in any case, this is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: to extract data [message #314534 is a reply to message #314529] Thu, 17 April 2008 01:39 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
and ADD ST BEFORE '&' AND omit 'S'.

Search for substr in oracle reference manual.

Regards

Raj
Previous Topic: ORA-01033-Error
Next Topic: Explain plan method
Goto Forum:
  


Current Time: Sun Dec 11 02:28:26 CST 2016

Total time taken to generate the page: 0.04394 seconds