Comparing 2 columns in the same table with the "Like" function

Question

I'm trying to come up with a way to query the values in two different columns in the same table where the result set will indicate instances where the value of columnB doesn't contain the value of columnA.

For example, my "Nodes" table contains columns "NodeName" and "DNS". The values should look similar to the following:

NodeName    DNS
Router1     Router1.mydomain.com

I want to run a query to show which rows have a DNS value that does not contain (or begin with) the value of the NodeName field. I think the query should function something similar to the following, but obviously I'm missing something with regard to the use of "Like" in this situation.

SELECT NodeName, DNS
WHERE DNS NOT LIKE 'NodeName%'

I'm using SQL Server 2005, and any suggestions would be greatly appreciated... :)

Answer 1

SELECT NodeName, DNS
WHERE DNS NOT LIKE NodeName + '%' AND DNS NOT LIKE '%' + NodeName + '%'

Your DNS LIKE 'NodeName%' is comparing the string literal "NodeName" with DNS

Edit:

Nissan Fan added NOT LIKE '%' + NodeName + '%' which would mean NOT LIKE NodeName + '%' is not needed.

It depends if want "contains" or "starts with": take your pick.

@Nissan Fan: thanks: you should post it as your own answer...