Home » SQL & PL/SQL » SQL & PL/SQL » search varchar2 feild and get numbers from it???
icon4.gif  search varchar2 feild and get numbers from it??? [message #226430] Fri, 23 March 2007 12:42 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Hello All,

I am required to write a before-delete trigger wherein, the trigger has to look at a column in a table of type varchar2(180), used for maintaining free form text...

example of the texts:

Example1:
202,101,345<enter>
zz-This is a default text


Example2:
202<enter>
zz-This is a default text


Example3:
zz-This is a default text


I need to know how can I go about parsing the column, and search for only numbers, and match those numbers against a lookup table. The lookup table contains the number-codes and the text for each of those number codes... there is no limit on how many numbers a user can add in the feild....

Plz help!

~Ozzy
Re: search varchar2 feild and get numbers from it??? [message #226431 is a reply to message #226430] Fri, 23 March 2007 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I want to be sure in understand your post.
You want/expect somebody to volunteer FOR FREE to do a work task that you are being paid to complete.
We (TINW) do the heavy lifting & you get the benefit from our time, knowledge & effort.
I, for one, will decline to contribute.

[Updated on: Fri, 23 March 2007 13:01] by Moderator

Report message to a moderator

Re: search varchar2 feild and get numbers from it??? [message #226432 is a reply to message #226430] Fri, 23 March 2007 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I accept, just send me your wages.

Re: search varchar2 feild and get numbers from it??? [message #226433 is a reply to message #226431] Fri, 23 March 2007 13:35 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
well, I am just looking for a direction, dont want you to write the complete trigger for me... this is just a part of the trigger that I am asking for a guidance...
Re: search varchar2 feild and get numbers from it??? [message #226435 is a reply to message #226430] Fri, 23 March 2007 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Above is the URL to the fine PL/SQL reference guide
http://asktom.oracle.com
contains many fine coding examples.
How is it that you seem to have a job as a programmer, but seem unable to parse through a single string variable looking for numbers?
This is usually done in Computer Science 101 - Beginning Programming.

Post your code that cleanly compiles & show via cut & paste how it functions (or not).

[Updated on: Fri, 23 March 2007 13:49] by Moderator

Report message to a moderator

Re: search varchar2 feild and get numbers from it??? [message #226436 is a reply to message #226433] Fri, 23 March 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

substr and instr are the functions to scan a string.
Loop on them and you will do it.

Regards
Michel
Re: search varchar2 feild and get numbers from it??? [message #226438 is a reply to message #226436] Fri, 23 March 2007 14:00 Go to previous message
ozzy80
Messages: 55
Registered: June 2005
Member
Thanks Michel!!!
Previous Topic: autotrace problem in oracle9i
Next Topic: Duplicates
Goto Forum:
  


Current Time: Mon Dec 05 03:04:51 CST 2016

Total time taken to generate the page: 0.11208 seconds