question

David 2 1 avatar image
David 2 1 asked

How To Create A Query To Use The Value From Another Table As A WHERE LIKE Clause?

How do I create a query that uses the value from another table as a WHERE LIKE clause?

For example I have a POSTCODE table consisting of postcode values such as 'DD', 'L', 'WN', etc... But how can I run a query on another table, say CUSTOMERS, by pulling out like values from the postcode one?

If it was hard coded it would read something like:

select * from CUSTOMERS where CUSTOMERS.POSTCODE like 'DD%' or CUSTOMERS.POSTCODE like 'L%' or CUSTOMERS.POSTCODE like 'WN%'

So far I have created a cursor however I am unsure how to get the like postcode values restrict on LIKE.

TIA.

t-sqlsql-server-2000
10 |1200

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

1 Answer

·
Benjamin avatar image
Benjamin answered

You could do something like this.

SELECT * FROM CUSTOMERS            
WHERE CUSTOMERS.POSTCODE IN (SELECT POSTCODE.CODE FROM POSTCODE)            

If you have duplicates in the POSTCODE table, you could add the distinct keyword.

SELECT * FROM CUSTOMERS            
WHERE CUSTOMERS.POSTCODE IN (SELECT DISTINCT POSTCODE.CODE FROM POSTCODE)            

Also you can put a WHERE clause on your sub-select like this. But instead of using a hard-coded value, you put a variable. This could alternatively be defined as a stored procedure.

DECLARE @Code varchar(5)            
SET @Code = 'DD'            
SELECT * FROM CUSTOMERS            
WHERE CUSTOMERS.POSTCODE IN (SELECT POSTCODE.CODE FROM POSTCODE            
    WHERE POSTCODE.CODE LIKE @Code + '%')            
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.