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.
works for a=applesauce and b=sauce
works for a=applesauce and b=apple
works for a=applesauce and b=les
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
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:-
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:
answered Nov 13 '09 at 06:04 AM
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.
answered Nov 12 '09 at 05:04 PM
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
answered Nov 12 '09 at 05:17 PM
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...
answered Nov 12 '09 at 06:25 PM
Matt Whitfield ♦♦