Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    16

    Unanswered: phone_num not like

    Hi,

    I am facing one issue with one requirement.

    Requirement is : I need to get the record count from a table where phone_num not like( 289,
    416
    519
    613
    647
    705
    807
    905 ) numbers..

    means.. I need get the record count where phone num should not start with these codes..

    How can I achieve it.. Can anyone please help me....

    Thanks,
    Rahul.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rahul678 View Post
    phone_num not like( 289,
    LIKE only works with string literals, not with numbers. Consider using NOT IN

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by n_i View Post
    LIKE only works with string literals, not with numbers. Consider using NOT IN
    Is it still the case with 9.7 implicit casting?? I do not have a 9.7 db(any db for that matter ) to test at the moment
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2009
    Posts
    16
    I can not use NOT IN because phone_nums will be like 289-394-942, 289-278-462,

    I need to match first 3 digits of phone number only..
    and there are so many area codes like 289,306,612 etc..

    I need to get the records which are not matched the above 3 digits of phone numbers.

    Please suggest some other solution..

    Thanks,Rahul.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    rahul678, If the column is a character string you could use:
    Code:
    SELECT COUNT(*) AS CNT
    FROM table-name
    WHERE col-name NOT LIKE '289%'
       OR col-name NOT LIKE '416%'
       OR col-name NOT LIKE '519%'
       OR col-name NOT LIKE '613%'
       OR col-name NOT LIKE '647%'
       OR col-name NOT LIKE '705%'
       OR col-name NOT LIKE '807%'
       OR col-name NOT LIKE '905%'
    If it is numeric you could use:
    Code:
    SELECT COUNT(*) AS CNT
    FROM table-name
    WHERE col-name NOT BETWEEN 2890000000 and 2899999999
       OR col-name NOT BETWEEN 4160000000 and 4169999999
       OR col-name NOT BETWEEN 5190000000 and 5199999999
       OR col-name NOT BETWEEN 6130000000 and 6139999999
       OR col-name NOT BETWEEN 6470000000 and 6479999999
       OR col-name NOT BETWEEN 7050000000 and 7059999999
       OR col-name NOT BETWEEN 8070000000 and 8079999999
       OR col-name NOT BETWEEN 9050000000 and 9059999999

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SUBSTR(str,1,3) not in ( 289,
    416
    519
    613
    647
    705
    807
    905 )

    Well, the performance is going to be poor ... If this is a frequent query, consider creating an area-code column to apply this predicate against
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyaram_s View Post
    Is it still the case with 9.7 implicit casting?? I do not have a 9.7 db(any db for that matter ) to test at the moment

    It does work, actually. Thanks for the idea.

    Code:
       Database Connection Information
    
     Database server        = DB2/NT 9.7.1
     SQL authorization ID   = ???
     Local database alias   = ???
    
    db2 => describe table emp;
    
                                    Data type                     Column
    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    EMPNO                           SYSIBM    DECIMAL                      5     0 Yes
    
      1 record(s) selected.
    
    db2 => select * from emp;
    
    EMPNO
    -------
        10.
        10.
    
      2 record(s) selected.
    
    db2 => select * from emp where empno like '1%';
    
    EMPNO
    -------
        10.
        10.
    
      2 record(s) selected.
    
    db2 => select * from emp where empno like '2%';
    
    EMPNO
    -------
    
      0 record(s) selected.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question What about this

    I like this:

    Code:
    with numbers(area_cd) as
    ( 
    select '289' from sysibm.sysdummy1 union all
    select '416' from sysibm.sysdummy1 union all
    select '519' from sysibm.sysdummy1 union all
    select '613' from sysibm.sysdummy1 union all
    select '647' from sysibm.sysdummy1 union all
    select '705' from sysibm.sysdummy1 union all
    select '807' from sysibm.sysdummy1 union all
    select '905' from sysibm.sysdummy1  
    )
    ,
    phone_book(phone_number) as
    (
    select '718-444-4444' from sysibm.sysdummy1 union all
    select '289-333-4444' from sysibm.sysdummy1 union all
    select '416-111-1111' from sysibm.sysdummy1 union all
    select '205-111-1111' from sysibm.sysdummy1 union all
    select '519-111-1111' from sysibm.sysdummy1 union all
    select '998-100-1111' from sysibm.sysdummy1 union all
    select '613-111-1111' from sysibm.sysdummy1 union all
    select '647-999-1111' from sysibm.sysdummy1 union all
    select '705-111-1111' from sysibm.sysdummy1 union all
    select '807-888-1111' from sysibm.sysdummy1 union all
    select '678-777-1111' from sysibm.sysdummy1 union all
    select '905-222-1111' from sysibm.sysdummy1  
    ) 
    select pb.* 
    from phone_book pb
    where not exists 
    (select 1 from numbers nm 
      where Locate(nm.area_cd, pb.phone_number) = 1 )
    Result:

    PHONE_NUMBER

    718-444-4444
    205-111-1111
    998-100-1111
    678-777-1111
    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •