question

David 2 1 avatar image
David 2 1 asked

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.

t-sqlsql-server-2000sub-querylike
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

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            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 2 1 avatar image
David 2 1 answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

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+'%'

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.