Home » SQL & PL/SQL » SQL & PL/SQL » use substr/instr [Merged]
use substr/instr [Merged] [message #300594] Sat, 16 February 2008 06:30 Go to next message
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
Junior Member
Hi,
I need some urgent help with this.I have a table that has
got the address field that looks like this
e.g
newoxford pa15071.. I need to extract the zip code from that
and put that value in a new column in the same table.I am not
sure how the str/instr function will be to get the exact
output.please send me your suggestions at the earliest..

Mandy
Re: use substr/instr [message #300597 is a reply to message #300594] Sat, 16 February 2008 08:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please read the OraFAQ Forum Guide before posting.

This is definitely not an EXPERT question.

For an accurate answer, you really should provide more details on your version of Oracle and exactly how the address field is formatted.
Re: use substr/instr [message #300612 is a reply to message #300597] Sat, 16 February 2008 14:20 Go to previous messageGo to next message
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
Junior Member
I am using Oracle 10g Database running on Windows xp.The existing address field is varchar2(50)and I will be calling a procedure that will need the last 5 digits from the exisiting
address field which has the city and state aswell and pass that
as a parameter and update the table accordingly.

So if the oldaddress coulmn in the table is
NEWOXFORD PA 15071,I will need 15071 as a parameter in the procedure I am calling.
Re: use substr/instr [message #300614 is a reply to message #300612] Sat, 16 February 2008 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SUBSTR

Regards
Michel
Re: use substr/instr [message #300625 is a reply to message #300614] Sat, 16 February 2008 17:48 Go to previous messageGo to next message
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
Junior Member
Hi,
By using substr alone its not giving me the right string.
eg:substr('string value',-5,5)does not give me the right string
also the length of the string will not be fixed in the table.

Thanks,
Mandy
Re: use substr/instr [message #300626 is a reply to message #300625] Sat, 16 February 2008 18:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Given what little you have provided, it does work, as demonstrated below:

SCOTT@orcl_11g> select substr ('NEWOXFORD PA 15071', -5, 5) from dual;

SUBST
-----
15071

SCOTT@orcl_11g>

So, there is apparently something that you have done wrong or something you have not told us. That is why you need to read the forum guidelines and post a copy and paste of what you actually did. The longer you delay posting a proper question, the longer it takes to get the answer that you need.

If you have an unformatted string, then you need to figure out what rules you can apply to extract the zip code. Oracle is not magic. You have to figure out ways to eliminate the characters before and after the zip code or search for five numbers in a row or some such thing.

I should also point out that this is a bad design to begin with. This is why cities, states, and zips and so forth should be stored in separate columns. However, I understand that sometimes you are stuck with extracting data from some legacy system or someone else's system.
Re: use substr/instr [message #388177 is a reply to message #300626] Mon, 23 February 2009 11:49 Go to previous messageGo to next message
dwidget71
Messages: 3
Registered: February 2009
Location: ND
Junior Member
Here you go. This will grab the last 5 characters of your string.

substr('NEWOXFORD PA 15071', length('NEWOXFORD PA 15071')-5,6)
Re: use substr/instr [message #388179 is a reply to message #388177] Mon, 23 February 2009 12:33 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
First of all, this is a year old thread.
Second, can you find out how your post differed from Barbara's? I surely cannot.
Re: use substr/instr [message #388182 is a reply to message #388179] Mon, 23 February 2009 12:42 Go to previous messageGo to next message
dwidget71
Messages: 3
Registered: February 2009
Location: ND
Junior Member
I don't think it is necessary to be so snotty to someone who is only trying to help.
Re: use substr/instr [message #388183 is a reply to message #388182] Mon, 23 February 2009 12:51 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
How did it help? Repeating someone else's answer?
Re: use substr/instr [Merged] [message #388185 is a reply to message #300594] Mon, 23 February 2009 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I don't think it is necessary to be so snotty
Snottiness is in the eye of the beholder.
Re: use substr/instr [message #388194 is a reply to message #388183] Mon, 23 February 2009 15:04 Go to previous messageGo to next message
dwidget71
Messages: 3
Registered: February 2009
Location: ND
Junior Member
joy_division wrote on Mon, 23 February 2009 12:51
How did it help? Repeating someone else's answer?


I misread Barbara's post so I thought the question hadn't been fully answered. I admit that I made a mistake, but the fact is that I was trying to be helpful and I don't see the need to belittle people when all they are trying to do is help.
Re: use substr/instr [message #388354 is a reply to message #388194] Tue, 24 February 2009 07:52 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
No problem. Point taken.
Previous Topic: Difference between primary key and unique index
Next Topic: SQL Question
Goto Forum:
  


Current Time: Tue Feb 11 12:47:52 CST 2025