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.
Answer by David 2 1 ·
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.
Answer by RickD ·
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.
Answer by Madhivanan ·
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+'%'
Answer by TG ·
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