CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tayyab
Tayyab

Reputation: 65

Is there a possibility to Avoid multiple "OR" statement in Microsoft SQL?

I have a query that has to filter our results from a text field based on certain keywords used in the textline .. currently the SQL statement looks like the below.

and (name like '%Abc%') or (name like '%XYZ%') or (name like '%CSV%')...

Is there a way to avoid multiple or statements and achieve the same results?

Upvotes: 2

Views: 2468

Answers (3)

GarethD
GarethD

Reputation: 69819

A slightly more shorthand way of doing this if you have a large amount of different patterns is to use EXISTS and a table value constructor:

SELECT  *
FROM    T
WHERE   EXISTS
        (   SELECT  1
            FROM    (VALUES ('abc'), ('xyz'), ('csv')) m (match)
            WHERE   T.Name LIKE '%' + m.Match + '%'
        );

A similar approach can be applied with table valued parameters. Since this is usually a requirement where people want to pass a variable number of search terms for a match it can be quite a useful approach:

CREATE TYPE dbo.ListOfString TABLE (value VARCHAR(MAX));

Then a procedure can take this type:

CREATE PROCEDURE dbo.GetMatches @List dbo.ListOfString READONLY
AS
BEGIN
    SELECT  *
    FROM    T
    WHERE   EXISTS
            (   SELECT  1
                FROM    @List AS l
                WHERE   T.Name LIKE '%' + l.value + '%'
            );
END

Then you can call this procedure:

DECLARE @T dbo.ListOfString;
INSERT @T VALUES ('abc'), ('xyz'), ('csv');
EXECUTE dbo.GetMatches @T;

Upvotes: 0

Christian Barron
Christian Barron

Reputation: 2755

Just to give you another option you could also try this, an IN statement mixed with a PATINDEX:

Select *
from tbl
Where 0 not in (PATINDEX('%Abc%', name), PATINDEX('%XYZ%', name), PATINDEX('%CSV%', name))

Upvotes: 0

Coltech
Coltech

Reputation: 1730

You could put your filter keywords into a table or temp table and query them like this:

select      a.*
from        table_you_are_searching a
inner join  temp_filter_table b
on          charindex(b.filtercolumn,a.searchcolumn) <> 0

Upvotes: 1

Related Questions