CODEWITHSUNDEEP
sql-servert-sql
Kjensen
Kjensen

Reputation: 12384

Less repetition in where-clauses when querying many columns

Take a standard broad search query...:

DECLARE @sq Varchar(50) = 'Desperate'

SELECT * 
FROM [UnbelievablyHotWomenOrAtLeastAcceptable] u
WHERE 
u.Address LIKE '%' + @sq + '%' OR
u.City LIKE '%' + @sq + '%' OR
u.firstname LIKE '%' + @sq + '%' OR
u.Lastname LIKE '%' + @sq + '%' OR
u.Email LIKE '%' + @sq + '%' OR
u.Notes LIKE '%' + @sq + '%'

Is there a way to make that query less of a copy+paste effort?

Something along the lines of

...WHERE (u.Address OR u.City OR u.firstname OR u.Lastname OR u.Email OR u.Notes) LIKE '%' + @sq + '%'

I know if I use full text search, CONTAINS and CONTAINSTABLE offers me a syntax for querying all columns in the table, that are in the full text catalog at once, but that is not what I am looking for.

Upvotes: 1

Views: 148

Answers (3)

Quassnoi
Quassnoi

Reputation: 425623

SELECT  *
FROM    [UnbelievablyHotWomenOrAtLeastAcceptable] u
WHERE   (
        SELECT  TOP 1 str
        FROM    (
                SELECT  Address AS Str
                UNION ALL
                SELECT  City
                UNION ALL
                SELECT  Firstname
                UNION ALL
                SELECT  Lastname
                UNION ALL
                SELECT  Email
                UNION ALL
                SELECT  Notes
                ) q
        WHERE   str LIKE '%' + @sq + '%'
        ) IS NOT NULL

Upvotes: 0

Kjensen
Kjensen

Reputation: 12384

Seems like the answer is "No".

Upvotes: 0

Andomar
Andomar

Reputation: 238176

You can create a view like:

CREATE VIEW dbo.UnbelievablyHotView
AS
SELECT id, Address as val FROM dbo.UnbelievablyHotWomen
UNION ALL
SELECT id, City as val FROM dbo.UnbelievablyHotWomen
UNION ALL
SELECT id, firstname as val FROM dbo.UnbelievablyHotWomen
UNION ALL
...

And then query on that with:

SELECT *
FROM UnbelievablyHotView
WHERE val like '%' + @sq + '%'

This would only save copy & paste if you do the query in multiple places.

Upvotes: 1

Related Questions