Menu
I'm trying to write a Select statement where I can see if one column is like part of another.
tblNames ID FullName FirstName 1 Mr. John Doe, CEO John 2 Mr. Jake Doe, Exec Jake 3 Mrs. Betty Smith, Chair Jill The query should return:
3 | Mrs.Betty Smith, Chair | Jill However mine just returns every row in the table:
SELECT ID, FullName, FirstName FROM tblNames WHERE '%' + FirstName + '%' not like Fullname Any ideas?
691
The SQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
Answer. Yes, within a WHERE clause you can compare the values of two columns. When comparing two columns in a WHERE clause, for each row in the database, it will check the value of each column and compare them.
Reverse the where, to something like this:
Fullname not like '%' + FirstName + '%' Try this:
SELECT * FROM tblNames WHERE ISNULL( CHARINDEX (FirstName , FullName),0) = 0 The CHARINDEX will be faster (more performant) than a LIKE clause, as it doesn't have to take wildcards into account. The sample data above with small numbers of rows won't show a performance benefit, but when in the millions of rows, CHARINDEX would perform better.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
