question

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.

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            

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.

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