question

user-470 avatar image
user-470 asked

Why use LIKE without a wildcard?

I have recently run across t-sql queries that use the LIKE keyword without a wild card, e.g. select * from SomeTable WHERE SomeColumn LIKE 'someValue' Is there any valid reason for doing this? Does this behave differently from simply using equal?
sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
There are differences, actually. In the specific example you give, I can't see a reason why there would be any difference from using an equals operator, and that would be faster. But the SQL below demonstrates an example of LIKE and = returning different results. CREATE TABLE #temp (nam [varchar](MAX)) INSERT INTO [#temp] ([nam]) VALUES ('hello') INSERT INTO [#temp] ([nam]) VALUES ('hello ') SELECT * FROM #temp WHERE [nam] = 'hello ' SELECT * FROM #temp WHERE [nam] LIKE 'hello ' The first result set returns two rows, because = will ignore trailing spaces, whereas the second result set returns one row, because all characters in the LIKE pattern are important. From [LIKE (Transact-SQL)][1] on MSDN: > When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. [1]: http://msdn.microsoft.com/en-us/library/ms179859.aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - great point, seems backwards logic: = should be more precise than LIKE, from an English grammar perspective.
2 Likes 2 ·
I guess that depends on how recently you've read animal farm... All strings are equal... but some are more equal than others.
2 Likes 2 ·
All this discussion of "LIKE" leads me to wonder... where's the "INNIT" assertion?
1 Like 1 ·
@Matt Whitfield Great point! There was a QotD on SSC site few months ago which was based on this difference. The question was asking to actually find some @a and @b values, but the idea was
declare @a varchar(10);
declare @b varchar(10);

select @a = 'A ', @b = 'A';

if @a = @b and @a like @b and @b NOT LIKE @a
    print 'the above is true';
else
    print 'the above is false';
It prints: the above is true
1 Like 1 ·
I stand corrected then. Thanks, Matt I did not know that.
0 Likes 0 ·
Show more comments
Tim avatar image
Tim answered
Sounds like sloppy coding.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@TRAD Very sloppy indeed! I remember inheriting a VB 6 app on the top Access 97 database. The app had an illness of notoriously querying data with **like 'some_value'** without the wildchar characters. While I was busy changing all such queries, the app still worked OK until the DB grew in size over 1 GB, which quickly prompted an upgrade to Access 2000. This upgrade brought it to a screeching halt. Access 2000 happened not to like any such queries with the LIKE without widchars and the queries started taking like 40 minutes to execute instead of the usual 1 or 2 seconds. This was pretty interesting experience :)
1 Like 1 ·
ThomasRushton avatar image
ThomasRushton answered
I quite often use this technique when writing parameterised stuff when I'm sure that what comes in may be perfectly valid with or without wildchars. But not when passing in a fixed string like that.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.