SQL Searching Multiple Words in a String

sql searching multiple words in a string

Select * from table where 
  columnname like'%David%' and 
  columnname like '%Moses%' and columnname like'%Robi%' 

Sql Search in a string for multiple words, knowing that some of them might not exist

You can use PATINDEX function, wchich returns index of a match in string. If there's no match it returns 0, so try the following script:

declare @x table(
ID int identity(1,1),
name varchar(30)
)

insert into @x values ('hi cat, I like dogs and owls'), ('hi cat'),('hey bat, cat, owl, dog'),('orange is sweet')

select ID from @x where PATINDEX('%cat%', name) + PATINDEX('%dog%', name) + PATINDEX('%bat%', name)  > 0

Another solution would be using LIKE operator:

select ID from @x where name LIKE '%cat%' or name LIKE '%dog%' or name LIKE '%bat%'

Answering your edit, first approach is the best. You achieve desired result by following query (unfortunately it gets little complicated):

select ID from @x 
where PATINDEX('%cat%', name) + PATINDEX('%dog%', name) + PATINDEX('%bat%', name)  > 0
order by case PATINDEX('%cat%', name) when 0 then 0 else 1 end + 
         case PATINDEX('%dog%', name) when 0 then 0 else 1 end +
         case PATINDEX('%bat%', name) when 0 then 0 else 1 end
desc

How to use LIKE in a query to find multiple words?

If the pattern to be matched is

string1<space>anything<space>string2

you can write:

like string1||' % '||string2

Use multiple words in FullText Search input string

You will have to do some pre-processing on your @Keyword parameter before passing it into the SQL statement. SQL expects that keyword searches will be separated by boolean logic or surrounded in quotes. So, if you are searching for the phrase, it will have to be in quotes:

SET @Keyword = '"this is a search item"'

If you want to search for all the words then you'll need something like

SET @Keyword = '"this" AND "is" AND "a" AND "search" AND "item"'

For more information, see the T-SQL CONTAINS syntax, looking in particular at the Examples section.

As an additional note, be sure to replace the double-quote character (with a space) so you don't mess up your full-text query. See this question for details on how to do that: SQL Server Full Text Search Escape Characters?

Multiple words search across multiple columns

If you use two words to search (one name and one last name but you don't know which is which), you can create a function like this:

CREATE FUNCTION twoWordsSearchForContacts(words text) RETURNS SETOF contact AS
$$

DECLARE

aWords text[] := string_to_array(words, ' ');

word1 text := aWords[1];
word2 text := aWords[2];

BEGIN

  RETURN QUERY SELECT * 
               FROM contact 
               WHERE ("lastName" ILIKE '%' || word1 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word2 || '%')
                       OR ("lastName" ILIKE '%' || word2 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word1 || '%');
END;

$$ LANGUAGE plpgsql;

Using the function:

SELECT * FROM twoWordsSearchForContacts('smi joh');

You can also split words directly in the query without using a function.

SQL search multiple values in same field

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

---

Related Topics