-
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?
Jeff Hunter
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|