I now want to select the prefix_id with the correct prefix,
select prefix_id where substr(prefix,?) like '077696666444'
I think I will need a substr here, but how do I manage the length of the prefix column etc.
Can anyone help? Thanks.
We deal with mobile phones and this is just an example, but I would want to select the row that has the exact prefix of 07769 (prefix_id is 1), and not 0776 or 077, where the phone number is 07769666644, or select the row with prefix of 077(prefix_id is 5) where phone number is 07743211111. Unfortunately we are using a third party application and it won't allow pl/sql (procedures etc), we can only use sql. Thanks.
Let me know what you think about this solution.
Select * from phone ;
select no, max(length( substr(no,1,length('&pattern')-r))) prefix
from phone , ( select rownum -1 r from tab )
where instr(no,substr('&pattern',1,length('&pattern')-r),1) >0
and r <= length('&pattern')
group by no
Thanks for all your help. I solved it by the following select statement:
where prefix =
where prefix = substr('077700006644',1,length(prefix))