use substr/instr [Merged] [message #300594] |
Sat, 16 February 2008 06:30  |
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 #300612 is a reply to message #300597] |
Sat, 16 February 2008 14:20   |
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 #300625 is a reply to message #300614] |
Sat, 16 February 2008 17:48   |
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   |
 |
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   |
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   |
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 #388194 is a reply to message #388183] |
Mon, 23 February 2009 15:04   |
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.
|
|
|
|