DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Query on where and like %

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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.



  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Question

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

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    This is a terrible design for searching a character field. It could be done most easily in a stored procedure. Good luck.

  4. #4
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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.


  5. #5
    Join Date
    Jul 2000
    Posts
    296
    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?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  7. #7
    Join Date
    May 2000
    Posts
    58
    I am not sure if " order by " is allowed in inline views.

  8. #8
    Join Date
    May 2000
    Posts
    58
    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

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  10. #10
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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
  •  


Click Here to Expand Forum to Full Width