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?
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
+1 - great point, seems backwards logic: = should be more precise than LIKE, from an English grammar perspective.
I guess that depends on how recently you've read animal farm... All strings are equal... but some are more equal than others.
All this discussion of "LIKE" leads me to wonder... where's the "INNIT" assertion?
@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
Sounds like sloppy coding.
@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 :)
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.
