phone_num not like

rahul678 · #1 (**permalink**) 04-20-10, 08:58

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.

n_i · #2 (**permalink**) 04-20-10, 09:19

Quote:

Originally Posted by rahul678

phone_num not like( 289,

LIKE only works with string literals, not with numbers. Consider using NOT IN

sathyaram_s · #3 (**permalink**) 04-20-10, 09:28

Quote:

Originally Posted by n_i

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

rahul678 · #4 (**permalink**) 04-20-10, 09:48

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.

Stealth_DBA · #5 (**permalink**) 04-20-10, 10:24

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

sathyaram_s · #6 (**permalink**) 04-20-10, 10:24

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

n_i · #7 (**permalink**) 04-20-10, 10:26

Quote:

Originally Posted by sathyaram_s

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.

Lenny77 · #8 (**permalink**) 04-20-10, 18:03

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:

Quote:

PHONE_NUMBER

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

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech