Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP - ignore spaces
REGEXP - ignore spaces [message #246663] Thu, 21 June 2007 10:32 Go to next message
Ric.hard
Messages: 5
Registered: June 2007
Junior Member

Please help, all I wanna do is:

From varchar2, eg.:
'Street name, 888/77, City, ZIP CODE'

take pieces of address. The only delimitter is comma.

What I'm trying to do is:
regexp_replace(address, '^(\w+),(\w+),(\w+),(\w+)$', '\1, \4, \3, \2'), but it doesn't work, becouse of spaces in my string. I don't know how to ignore them? Street name can be with space, also city etc...

Thanks for your help!

Richard
Re: REGEXP - ignore spaces [message #246682 is a reply to message #246663] Thu, 21 June 2007 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Don't just show your "solution" explain your functional need.

Regards
Michel
Re: REGEXP - ignore spaces [message #246809 is a reply to message #246663] Fri, 22 June 2007 01:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As Michel mentioned, it may help if you specify your requirements more precisely.

From what I gather, you may be looking for something like this ??

SQL> select regexp_replace('Street name, 888/77, City with spaces, ZIP CODE',
  2     '^([^,]+)(.+)(, [^,]+)$','\1\3\2') reformatted from dual;

REFORMATTED
-----------------------------------------------
Street name, ZIP CODE, 888/77, City with spaces

Re: REGEXP - ignore spaces [message #246860 is a reply to message #246809] Fri, 22 June 2007 04:49 Go to previous messageGo to next message
Ric.hard
Messages: 5
Registered: June 2007
Junior Member

Thank you for your help! As you can see I'm not familiar with regular expressions..

What I exactly need is:

I have address in one field named "comments". I need to separate sections from address:

select comments,
regexp_replace(comments, 'question for you') street,
regexp_replace(comments, 'question for you') streetno1,
regexp_replace(comments, 'question for you') streetno2,
--only if exists, it means if there is mark "/", take chars after it),
regexp_replace(comments, 'question for you') city,
regexp_replace(comments, 'question for you') zip_code
from cust_order co
where comments like '%,%'

example of data:
Skutecskeho, 2, Praha 6, 160 00
Vinohradska, 2375/198, Praha 3, 130 00

Sorry for inaccuracy..
Re: REGEXP - ignore spaces [message #246867 is a reply to message #246860] Fri, 22 June 2007 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand, you have several fields separated by "," and you want to separate them.
SUBSTR and INSTR will do the trick.

Regards
Michel
Re: REGEXP - ignore spaces [message #246872 is a reply to message #246867] Fri, 22 June 2007 05:09 Go to previous messageGo to next message
Ric.hard
Messages: 5
Registered: June 2007
Junior Member

Of course it will, but this is not the "nice" way:-) I know that Oracle 10 support regular expressions, so why don't use them?

If I use substr a instr, can you imagine how loooooong the command would be?
Re: REGEXP - ignore spaces [message #246894 is a reply to message #246872] Fri, 22 June 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Long? Do you mean the number of characters? Maybe.
But surely taking much less time than regexp during execution.
How many times do you write a query?
How many times do you execute a query?

Regards
Michel
Re: REGEXP - ignore spaces [message #246901 is a reply to message #246894] Fri, 22 June 2007 07:28 Go to previous messageGo to next message
Ric.hard
Messages: 5
Registered: June 2007
Junior Member

Yes, I mean number of characters.
You are probably right, so my looong SQL looks like:

select comments,
trim(substr(comments,1,instr(comments,',')-1)) in1,
trim(substr(comments,instr(comments,',',1,1)+1, instr(comments,',',1,2) - instr(comments,',',1,1) - 1)) in2,
trim(substr(comments,instr(comments,',',1,2)+1, instr(comments,',',1,3) - instr(comments,',',1,2) - 1)) in3,
trim(substr(comments,instr(comments,',',1,3)+1, length(comments) - instr(comments,',',1,2) - 1)) in4
from cust_order
where comments like '%,%'

If somebody can write reg exp for that, I'll be happy.
Re: REGEXP - ignore spaces [message #246905 is a reply to message #246901] Fri, 22 June 2007 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is really more efficient than regexp.
Does it look so awful?

Regards
Michel

[Updated on: Fri, 22 June 2007 07:42]

Report message to a moderator

Re: REGEXP - ignore spaces [message #246922 is a reply to message #246905] Fri, 22 June 2007 08:21 Go to previous message
Ric.hard
Messages: 5
Registered: June 2007
Junior Member

No, I will leave it as I wrote. But I just want to know, how to solve the problem with reg exps..
Previous Topic: display in next line
Next Topic: Series of numbers
Goto Forum:
  


Current Time: Thu Dec 08 19:56:23 CST 2016

Total time taken to generate the page: 0.15486 seconds