Use LIKE where matching pattern is a column value plus a wildcard?

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.

more ▼

asked Nov 12 '09 at 04:35 PM in Default

DonnaB gravatar image

82 2 2 2

(comments are locked)
10|1200 characters needed characters left

10 answers: sort voted first

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:


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 + '%'


WHERE CHARINDEX(column_value, 'value') > 0 
more ▼

answered Nov 13 '09 at 05:35 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

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],  
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]

SELECT * FROM [Person].[Contact] AS c
WHERE [EmailAddress] LIKE [FirstName]+'%'


more ▼

answered Nov 13 '09 at 06:04 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Here's the little block of code I'm using: select dm.ConstituentID RE_ID ,dm.FullName RE_Name ,c.compare_name Vendor_Name from NONPROFIT_DW.dbo.DIM_Constituent dm inner join IMT_DEV.dbo.comparison_table c --on substring(dm.FullName,1,5) = substring(c.compare_name,1,5) on dm.FullName like '%' + c.compare_name + '%' and dm.KeyIndicator = 'O' order by Vendor_Name

So, the substring line works and returns rows; the "like" line returns nada. I'm going to try the bracket thing and see what happens...we'll see!

Thank you - stay tuned.
Nov 13 '09 at 10:35 AM DonnaB
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 12 '09 at 05:04 PM

TimothyAWiseman gravatar image

15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 12 '09 at 05:17 PM

Jeff Oresik gravatar image

Jeff Oresik
138 4 4 6

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 12 '09 at 06:25 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 12 '09 at 04:35 PM

Seen: 19331 times

Last Updated: Nov 13 '09 at 02:42 PM