LIKE function in Select statement

[hecheng]

Could anyone explain to me how does LIKE function
work in the select statement??

Thank You!!

 

[dan1967]

Sure. A LIKE operator tells SQL that you want to bring in records that are similar, not an exact match, to whatever value you determine. For example, you are using a database of cookbooks. You only want the titles that have apple in it:

SELECT BookTitle, Author
FROM CookBooks
WHERE BookTitle LIKE '%apple%'

The "%" is the SQL wildcard for any character(s).

Hope this helped.

 

[clarkin]

what dan1967 said and also:
the % will allow any amount (0+) of any character and the _ will allow exactly 1 of any character.

so if you SELECT Title FROM Books WHERE Title LIKE '%clark_n'

you could get the following results
'oh look its clarkin'
'clarkRn'
' clark n'
but you won't get any of the following:
'clarkin bla bla'
'clarkn'

Also try looking at the MS Books Online Transact SQL reference, it should explain how to use SQL keywords with SQL Server.

 

[SQLSister]

Be careful of using like '%anytext%'

If you put the wildcard first, then SQL Server cannot use indexes and must do a table scan. This is inefficient and will slow down your application. If you frequently need to do this sort of thing, you should look at full text indexing instead.

 

[slowface]

there is also the possibility to limit some characters on a position in your string.
e.g.:

LIKE '[12A]&'

means select everything that starts with eather '1', '2' or 'A'

you can also specify ranges:

LIKE '[5-9]&'

means everything that starts with number between 5 and 9.