Query on where and like %

[ssmith]

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.

[marist89]

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

[kmesser]

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

[ssmith]

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.

[akkerend]

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?

[chrisrlong]

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

[Victoria]

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

[Victoria]

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

[chrisrlong]

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

[ssmith]

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