| search for the first occurrence of a number in a given string [message #20481] |
Tue, 28 May 2002 09:34  |
sfhollands
Messages: 15
Registered: October 2001
|
Junior Member |
|
|
I want to find the occurrence of the first number in a string.
the query goes something like this
select party_name
, d.start_date
,l.room_number
,substr(P.DESCRIPTION, INSTR(P.DESCRIPTION,'')+1,2) as text
from party p,date_table d, l location where
p.party_id = d.party_id and
p.party_id = l.party.id and
l.room_st in ('AD','SF','TH','PW') and
d.start_date = to_char(sysdate,'DD-MON-YY')
actually instead of the above instr statement, i want the substr from the frist occurrence of the number in the DESCRIPTION field
Any help will be appreciated.
thanks,
|
|
|
|
| Re: search for the first occurrence of a number in a given string [message #20486 is a reply to message #20481] |
Tue, 28 May 2002 13:02  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
You'll almost certainly find that using built in Oracle functions will be fatser than al PL/SQL solution.
-- translate all characters 1-0 into a 5
select translate('aaa123bbb456ccc7890ddd~!@','1234567890','5555555555') phone from dual;
aaa555bbb555ccc5555ddd~!@
--find the first occurrence of '5' in the string
select instr(translate('aaa123bbb456ccc7890ddd~!@','123456789
0','5555555555'), '5', 1) from dual
4
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
