order of LIKE values

[AndyApp]

I've got a search page and whatever the user puts in is subsequently built into a SQL statement in the LIKE clause. So if someone wants to search for "peter piper" my SQL looks like

LIKE '%peter%%piper%'

this is great however some of the entries are 'piper, peter' and the statement doesn't seem to bring these back. Is there a way to do this easily?

 

[SQLDenis]

split the string and do something like

create table #t (field1 varchar(500))
insert into #t
select 'piper, peter'

select * from #t
where field1 LIKE '%peter%'
and field1 like '%piper%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Not very useful but just for sport )

create table #t (field1 varchar(500))
insert into #t
select 'piper, peter' UNION ALL
select 'peter  piper'

select * from #t
where field1  LIKE '%[p][ei][tp][e][r]%'
drop table #t

Borislav Borissov

 

[SQLSister]

Remember that the like statement with a wildcard as the first character means the indexes cannot be used. When we have a requirement to do this kind of search, usually I search for the exact match first and then use the like search only if I get no records inthe exact search.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief