string_location:=MIN(DECODE(INSTR(my_string,'0'),0,999,INSTR (my_string,'0')),DECODE(INSTR(my_string,'1'),0,999,INSTR(my_string ('1')),....,DECODE(INSTR(my_string,'9'),0,999,INSTR(my_string,'9')));Another possibility would be:
string_location:=INSTR(TRANSLATE(UPPER(my_string), '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'NNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAA'), 'A',-1);Like this you get the last character in the string. What do you think about it?
I think it is great!!! But shouldn't the INSTR function be looking for 'N' instead? Just a minor detail.
There seems to always be more than one way to get the job done these days. And one persons resolution may not always be the best. I never would have thought about using the TRANSLATE function for solving this problem. But now I probably won't overlook it in the future!
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2002