Using Contains how to search for more than one value-CodePudding

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'

CodePudding user response：

This should work:

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

But in your case you dont want to use 2 contains so:

Alternatively, you could add a new computed column with a full text index on it.

Add a column like this:

computedCol AS fName  ' '   sName

And create the full text index:

CREATE FULLTEXT INDEX ON Person(computedCol LANGUAGE 1033)
KEY INDEX pk_Person_yourPrimaryKeyName

Then you can do this:

SELECT * FROM dbo.Person
WHERE CONTAINS(*, 'John AND Doe')

Hope it works.

CodePudding user response：

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 :)