Query on where and like %

I have a table with the following:
select * from test;

PREFIX PREFIX_ID
--------------- ------
07769 1
07770 2
07771 3
0776 4
077 5

I now want to select the prefix_id with the correct prefix,
e.g.
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.

I'm not really sure what you are asking. Your data doesn't support your result. Could you elaborate?

This is a terrible design for searching a character field. It could be done most easily in a stored procedure. Good luck.

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.

With
SELECT prefix_id
FROM test
WHERE INSTR('077696666444',prefix,1) > 0
you get the prefix_ids of possible prefixes for the number.

How do you know 07769 is the correct prefix, and not 077?

Ahhhh.....

I think I finally get it. Check my math on this one ssmith.

IF the number begins with '07769...', then you want to put that into a query and get out the prefix 1 - 00769.

However, IF the number begins with '07768...', then return prefix 4 - 0076

Finally, IF the number begins with '07792...', then return prefix 5 - 077

Am I right?

If so, I will further assume that, at some point, you will wish to do this across multiple numbers, which may be stored in another table (PhoneNum_T)

If so...

SELECT
---*
FROM
---(
---SELECT
------PN.PHONENUMBER,
------T.PREFIX_ID
---FROM
------TEST_T---------T,
------PHONENUMBER_T---PN,
---WHERE
------PN.PHONENUMBER---LIKE---T.PREFIX || '%'
---ORDER BY
------LENGTH(T.PREFIX) DESC
---)
WHERE
---ROWNUM = 1

This should get you what you want and allow the use of an index.

HTH,

- Chris

I am not sure if " order by " is allowed in inline views.

Let me know what you think about this solution.
Select * from phone ;
No
-----
07769665
07769666
0779666
0796665
0966677

define pattern='07769'
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

NO PREFIX
---------- ----------
07769665 5
07769666 5
0779666 3
0796665 2
0966677 1

Quote:

---

Originally posted by Victoria
I am not sure if " order by " is allowed in inline views.

---

It is in 8i.

What version are you using?

- Chris

Thanks for all your help. I solved it by the following select statement:
select *
from test
where prefix =
(select max(prefix)
from test
where prefix = substr('077700006644',1,length(prefix))
)
Rgds.
Sheryl