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

Posted by Vic on Stack Overflow See other posts from Stack Overflow or by Vic
Published on 2010-03-08T18:14:37Z Indexed on 2010/03/08 18:21 UTC
Read the original article Hit count: 358

Filed under:

sql

|

pattern-matching

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... :)

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about pattern-matching

A couple of pattern matching issues with pattern matching in Lua

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm fairly new to lua programming, but I'm also a quick study. I've been working on a weather forecaster for a program that I use, and it's working well, for the most part. Here is what I have so far. (Pay no attention to the zs.stuff. That is program specific and has no bearing on the lua coding… >>> More
pattern matching in .Net consistent with IsolatedStorageFile.GetFileNames() pattern matching

as seen on Stack Overflow - Search for 'Stack Overflow'
Is the pattern matching logic used by this API exposed for reuse somewhere in the .Net Framework? Something of the form FilePatternMatch( string searchPattern, stringfileNameToTest ) is what I'm looking for. I'm implementing a temporary workaround for WP7 not filtering the results for this overload… >>> More
pattern matching in Bash

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, Here is an example to get different parts of a filename bash-3.2$ pathandfile=/tmp/ff.txt bash-3.2$ filename=$(basename $pathandfile) bash-3.2$ echo $filename ff.txt bash-3.2$ echo ${filename##*.} txt bash-3.2$ echo ${filename%.*} ff I was wondering what does ##… >>> More
What is a 'Pattern Matching' in functional languages?

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm reading about functional programming (in academic purpose) and I've noticed that Pattern Matching is mentioned in many articles as one of the core features of functional languages. Can someone explain for a java/c++/javascript developer what does it mean? >>> More
Lua pattern matching vs. regular expressions

as seen on Stack Overflow - Search for 'Stack Overflow'
hello, i'm currently learning lua. regarding pattern-matching in lua i found the following sentence in the lua documentation on lua.org: Nevertheless, pattern matching in Lua is a powerful tool and includes some features that are difficult to match with standard POSIX implementations. as i'm… >>> More