Search code examples
sqlsql-server-2016

Using Contains how to search for more than one value

Here is what I am trying to do:

select * from Person where CONTAINS((fName,sName),'John AND Doe')

So I am trying to search for John Doe but I get that I cannot use AND here (just showing my chain of thought) so how can I search for John in the fName and Doe in the sName ? But I don't want to use "Contains" twice like this:

SELECT * FROM Person 
WHERE CONTAINS((fName), 'John')
AND CONTAINS((sName), 'Doe');

Since we can have

(fName,sName)

but I cannot use

'John','Doe'/'John' AND 'Doe'

Solution

  • Your statement

    SELECT * FROM Person 
WHERE CONTAINS((fName), 'John')
AND CONTAINS((sName), 'Doe');

    can't compile because CONTAINS returns a number, not a boolean.

    It shall respond,on oracle, with this kind of error

    ORA-00920: opérateur relationnel non valide
00920. 00000 -  "invalid relational operator"

    (the relational operator error's cause is not the AND's existence, it's because you try to AND two numbers)

    What do you intend to do? If you want to select the line in the person table whose fName columns contains the substring John and whose sName column contains the substring Doe, you may use the like operator, which uses % as a wildcard.

    SELECT * FROM Person
WHERE fName LIKE '%John%'
and sName LIKE '%Doe%'

    I don't practice much the CONTAINS method, but if you desire to use it, according to documentation, you should use something like

    SELECT * FROM Person
WHERE CONTAINS(fName, 'John') > 0
and CONTAINS(sName,'Doe') > 0

    If you really don't want to use the AND operator (for whatever obscure reason like proving a sql injection filter is bad), you can use this trick : compare a concatenation of the 2 columns and the like operator like so

    SELECT * FROM Person 
WHERE fName || sName like '%John%Doe%';

    but this last trick will also match the line where fname is John Doe and sName is Jean-Michel :)