Is there an advanced like function where i can get results even if one word matches

Question

hello I am trying to achieve something like this should give results if

where 'Robert Doug' like '%Robert'

or

where 'Robert Doug' like '%Doug Robert'

if users enters first name or last name and if it matches in that whole string it should return the record. Any help is appreciated.

for the first instance `LIKE %Robert%`, for the second `LIKE '%Doug%' AND LIKE '%Robert%'` — PlantTheIdea, Oct 17 '13 at 21:40
have a look at http://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words — John Bingham, Oct 18 '13 at 02:36

score 0 · Answer 1 · answered Oct 18 '13 at 05:53

0

Try like this

SELECT * FROM [YOUR TABLE_NAME] WHERE [COLUMN_NAME] LIKE ('%'+[USER PROMPT]+'%')

answered Oct 18 '13 at 05:53

R S P

997
5
8

score 0 · Answer 2 · answered Oct 18 '13 at 06:05

0

Or try this..

SELECT PATINDEX('%Robert%',NAME_COLUMN)
FROM [YOUR TABLE_NAME]

Or..

SELECT NAME_COLUMN
FROM [YOUR TABLE_NAME]
WHERE PATINDEX('%Robert%',NAME_COLUMN)<>'0'

answered Oct 18 '13 at 06:05

Leptonator

3,379
2
38
51

score 0 · Answer 3 · answered Oct 18 '13 at 17:32

Thank you all i tried something like this

WHERE ( d_searchJobTitle.term like '%'+case when CHARINDEX(' ','implementation analyst') >0 then SUBSTRING('implementation analyst',1,CHARINDEX(' ','implementation analyst')) else 'implementation analyst' end+'%' and d_searchJobTitle.term like '%'+ case when CHARINDEX(' ','implementation analyst') >0 then SUBSTRING('implementation analyst',CHARINDEX(' ','implementation analyst'),len('implementation analyst')) else 'implementation analyst' end

+'%' )

Is there an advanced like function where i can get results even if one word matches

3 Answers3