Compare 2 columns in the same table with the How function

Question

I am trying to find a way to query values ​​in two different columns in the same table, where instances in which the value of columnB does not contain the value of columnA will be indicated in the result set.

For example, my Nodes table contains the NodeName and DNS columns. The values ​​should look something like this:

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 does not start) the value of the NodeName field. I think the query should have something similar to the following, but obviously I'm missing something regarding the use of “Like” in this situation.

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

I am 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%' compares the string literal "NodeName" with DNS

Edit:

Nissan Fan added NOT LIKE '%' + NodeName + '%' , which would mean that NOT LIKE NodeName + '%' not required.

It depends on whether you want to “contain” or “start with”: take your choice.

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

Answer 2

 SELECT USR.USER_ID, USR.LDAP_TITLE, LRM.LDAP_ROLE, LRM.ROLE_ID, RLS.PRIMARY_ROLE FROM USERS USR, NEW_LDAP_ROLE_MATCH LRM, NEWROLES RLS WHERE (INSTR(USR.LDAP_TITLE,LRM.LDAP_ROLE) > 0) AND LRM.ROLE_ID = RLS.ROLE_ID; 

It took a long time to run the script. See the link to sql queries below. http://www.anaesthetist.com/mnm/sql/query.htm