Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full Text Index / Contains Expand / Collapse
Author
Message
Posted Friday, December 06, 2013 2:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:04 AM
Points: 18, Visits: 90
How do I optionally include a Contains search in my query, based on the input? In the below situation, I don't want to search if the criteria is empty.
In non-FTS I would do the following:

DECLARE @Criteria1 varchar(10) = 'find',
@Criteria2 varchar(10) = ''

SELECT *
FROM TableX
WHERE (@Criteria1='' OR Column1=@Criteria1)
AND (@Criteria2='' OR Column2=@Criteria2) --Correctly returns True, if no criteria supplied

How to implement this using Contains? I tried different approaches, like:

SELECT *
FROM TableX
WHERE (CONTAINS(Column1, @Criteria1))
AND (CONTAINS(Column2, @Criteria12)) --Error regarding no search word

SELECT *
FROM TableX
WHERE (CONTAINS(Column1, @Criteria1))
AND (CONTAINS(Column2, @Criteria12)) --Returns False, excluding the whole record (unwantedly)

SELECT *
FROM TableX
WHERE (@Criteria1='' OR CONTAINS(Column1, @Criteria1))
AND (@Criteria2='' OR CONTAINS(Column2, @Criteria12)) --Runs very slow and ineficiently

Do I have to create a separate query for echt combination of parameters, or is there a certain writing in the Contains statement? In other words, how do I get Contains return True immediately, when the criteria is empty?
Post #1520464
Posted Tuesday, December 10, 2013 12:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:04 AM
Points: 18, Visits: 90
Anybody?

I now have the following, which performs better, but doesn't seem optimal to me:
DECLARE @AllIDs table (ID int PRIMARY KEY)
IF @Criteria1<>'' AND @Criteria2=''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)

IF @Criteria1='' AND @Criteria2<>''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column2, @Criteria2)

IF @Criteria1<>'' AND @Criteria2<>''
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)
INTERSECT
SELECT ID FROM TableX WHERE CONTAINS(Column2, @Criteria2)

SELECT *
FROM TableX
WHERE ID IN (SELECT ID FROM @AllIDs)

The downsides of this methodology are:
- 2 searches in TableX: one for filling @AllIDs, and another one for actually retrieving the data
- If there are more optional search criteria, the IF statements will explode (although there are other writings to apply then)

Although the above works, I cannot imagine that there is a better way, built into Full Text Search functions.

Help is appreciated!
Post #1521412
Posted Tuesday, December 10, 2013 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:49 AM
Points: 6,798, Visits: 6,272
This is one of those 'it depends'

Doing two separate queries is not necessarily worse than one, I have in the past done two and found no problem with performance, in fact sometimes it is better.

If you are worried about doing more than one query then join the criteria together before the query, eg

IF @Criteria1<>'' AND @Criteria2<>''
SET @Criteria1 = @Criteria1 + ' AND ' + @Criteria2
ELSE
SET @Criteria1 = @Criteria1 + @Criteria2
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX WHERE CONTAINS(Column1, @Criteria1)

p.s. The only way to find the optimum solution is to try different methods and see which has the best performance / plan that meets your needs.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1521497
Posted Tuesday, December 10, 2013 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:04 AM
Points: 18, Visits: 90
Hi David,

Thanks for your reply.
Your solution won't work, I'm affraid, because @Criteria1 applies to Column1, and @Criteria2 applies to Column2. When one of the @Criteria parameters is empty, it should be ignored.

Regards, Juul
Post #1521507
Posted Tuesday, December 10, 2013 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
wouldn't switching to a union work, i think? i think the AND would short circuit any table scan right away, and will perform a bit better.
SELECT
*
FROM TableX
WHERE @Criteria1 <> ''
AND CONTAINS(Column1, @Criteria1))

UNION

SELECT
*
FROM TableX
WHERE @Criteria2 <> ''
AND CONTAINS(Column2, @Criteria2))




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521509
Posted Tuesday, December 10, 2013 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:49 AM
Points: 6,798, Visits: 6,272
Lowell (12/10/2013)
wouldn't switching to a union work, i think? i think the AND would short circuit any table scan right away, and will perform a bit better.
SELECT
*
FROM TableX
WHERE @Criteria1 <> ''
AND CONTAINS(Column1, @Criteria1))

UNION

SELECT
*
FROM TableX
WHERE @Criteria2 <> ''
AND CONTAINS(Column2, @Criteria2))




That will still produce the message as the CONTAINS will still be evaluated even if criteria is blank



Far away is close at hand in the images of elsewhere.

Anon.

Post #1521520
Posted Tuesday, December 10, 2013 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:49 AM
Points: 6,798, Visits: 6,272
Juul van Iersel (12/10/2013)
Hi David,

Thanks for your reply.
Your solution won't work, I'm affraid, because @Criteria1 applies to Column1, and @Criteria2 applies to Column2. When one of the @Criteria parameters is empty, it should be ignored.

Regards, Juul


Ooops! Sorry missed that.

try this

IF @Criteria1='' SET @Criteria1 = '*'
IF @Criteria2='' SET @Criteria2 = '*'
INSERT INTO @AllIDs (ID)
SELECT ID FROM TableX
WHERE CONTAINS(Column1, @Criteria1)
OR CONTAINS(Column2, @Criteria2)

This will not select any rows if criteria is '*' but using or at least one will select something.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1521523
Posted Tuesday, December 10, 2013 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:04 AM
Points: 18, Visits: 90
Hi Lowell,

i think the AND would short circuit any table scan right away, and will perform a bit better.

That indeed did the trick, I was sort of looking for, thanks a lot!!!
Post #1521540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse