We have two lists to compare, so I need to compare the data in the column against the data in the other column, and I'd like to use LIKE to do it. I used the syntax of "where col1 like col2 + '%' and it doesn't bomb, but it doesn't return any rows, either, and it should.
I'm not sure exactly what you consider a match, but I'll try to give some options.
a LIKE '%' + b
works for a=applesauce and b=sauce
a LIKE b + '%'
works for a=applesauce and b=apple
a LIKE '%' + b + '%'
works for a=applesauce and b=les
If you just want to compare without leading and trailing spaces, try this:
LTRIM(RTRIM(a)) = LTRIM(RTRIM(b))
If your database is case-sensitive, you may need to use UPPER() or LOWER().
As others have suggested, if the column data types are fixed-width (char), you should use RTRIM for any of these options.
EDIT (solution for Atoz): try
WHERE 'value' LIKE '%' + column_value + '%'
or
WHERE CHARINDEX(column_value, 'value') > 0
That should work.
Could there be something else preventing it from returning rows? Some other condition in the where clause? Remember also, that is looking for places where col2 is a subset of col1, but it will not return incidents where col1 is a subset of col2.
try where col1 like '%'+[col2]+'%'
You can take the wildcards away as you see fit. There may be an issue with your actual column name needing a bracket.
Works in SQL 2005
Personally I have always found LEFT(col, length) to be quicker than LIKE 'value%' - but others swear blind that the exact opposite is true. If you just want the value you're searching for to be at the start, it's worth trying, because the performance of both methods can be variable...
Just a thought - what data types are col1 and col2 ? If they are char then you may need to trim them to make the compare accurate...
Try selecting col1, col2 + '%' straight from the table to see what strings is being used:-
SELECT col1,
ltrim(rtrim(col1)) as [Trimmed1],
col2,
ltrim(rtrim(col2)) as [Trimmed2],
col2 + '%'
from mytable
You should then see what matching is going on and shed some like on why you get 0 rows...
This works as you describe needing yours to work:
USE [adventureworks]
GO
SELECT * FROM [Person].[Contact] AS c
WHERE [EmailAddress] LIKE [FirstName]+'%'
Jonathan
I'd definitely be interested in the datatypes. Have you tried moving the location of DM.KeyIndicator = 'O' to the WHERE clause? Sometimes that positioning does make a difference.
We have a similar problem here. The main difference is that we're using MS-Dynamics. This forces the character fields to be CHAR and not VARCHAR, or NVARCHAR (Dynamics does not support Unicode). Because of this we need to do LTRIM or RTRIM on the fields when we're using one like another.
Here is the situation..
=============
id key
=============
1 bo
2 fr
=============
..and i would like to query something like " where %column% = criteria.
Example: If i query for 'aboz', it would return row 1 (from above table) as result and if i query for 'fried', it would return row 2 (from above table) as result. It's the opposite with common situation. Is this the similar problem with yours? I've been looking for solution from weeks ago..
@atoz
SELECT Key FROM AboveTable WHERE 'fried' LIKE '%'+[Key]+'%'
