x

How To Write A Query To Work With A NOT LIKE Subquery?

How can I write the following query to work with a NOT LIKE subquery?

                    
SELECT custno                       
FROM customers                      
WHERE postcode not like                       
(SELECT postcode FROM mail_postcodes)                    

The MAIL_POSTCODES table contains values like 'L','BD','WF' however the values in this table are prone to change so the query CANNOT be hardcoded like:

                    
SELECT custno                       
FROM customers                      
WHERE postcode not like 'L%'                      
and postcode not like 'BD%'                      
and postcode not like 'WF%'                    

So how is it possible to write a NOT LIKE subquery with values pulled from that table?

Thanks in advance.

more ▼

asked Dec 09, 2009 at 07:58 AM in Default

David 2 1 gravatar image

David 2 1
412 46 50 52

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

You can't, but you can do exact matches and disregard these records. one of these may help.

SELECT custno            
FROM customers            
WHERE postcode not in            
(SELECT postcode FROM mail_postcodes)            
            
SELECT custno            
FROM customers            
WHERE not exists            
(SELECT postcode FROM mail_postcodes)            

Otherwise you need to define your search criteria a little better, is it the first char, the first two etc? Or have another table with the values you want to disregard, then use dynamic sql to build your query.

more ▼

answered Dec 09, 2009 at 08:16 AM

RickD gravatar image

RickD
1.7k 1 1 4

(comments are locked)
10|1200 characters needed characters left

Actually it is possible. This is how I got round it by creating a cursor which inserted into a temporary table if it matched a like. I then compared the temporary table with the actual one to get the unmatched postcodes:

            
DECLARE @postcode VARCHAR(10)              
DROP TABLE CUSTOMERS_TEMP              
SELECT * INTO CUSTOMERS_TEMP FROM CUSTOMERS WHERE 1=2              
DECLARE cur_1 CURSOR FAST_FORWARD FOR              
SELECT postcode FROM mail_postcodes              
            
OPEN cur_1              
            
FETCH NEXT FROM cur_1            
INTO @postcode              
WHILE @@FETCH__STATUS = 0              
BEGIN              
PRINT @postcode              
INSERT INTO CUSTOMERS_TEMP              
SELECT * FROM CUSTOMERS              
WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(            
LEFT(postcode,2),1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''')   LIKE @postcode              
FETCH NEXT FROM cur_1              
INTO @postcode              
END              
CLOSE cur_1              
DEALLOCATE cur_1              
SELECT * FROM CUSTOMERS               
WHERE custno NOT IN (SELECT custno FROM CUSTOMERS)              

There is probably a quicker easier way but this was my solution.

more ▼

answered Dec 10, 2009 at 06:41 AM

David 2 1 gravatar image

David 2 1
412 46 50 52

The Cursor will be an order of magnitude slower than a set-based solution - but for a small number of records that won't matter :)
Dec 10, 2009 at 04:05 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

The problem is that your customers table should store postcode.postcode as a separate column and store the suffix in a different column.

try this:

select c.custno
from customers c
left outer join postcode p
on c.postcode like p.postcode + '%'
where p.postcode is null
more ▼

answered Dec 09, 2009 at 01:31 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

Have you tried this?

SELECT * FROM CUSTOMERS as c left join mail_postcodes as pc
on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( LEFT(c.postcode,2),1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''') LIKE pc.postcode+'%'

more ▼

answered Dec 10, 2009 at 10:07 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x476
x46
x22

asked: Dec 09, 2009 at 07:58 AM

Seen: 6016 times

Last Updated: Jan 21, 2010 at 10:11 PM