0

Will there be any other ways instead of writing the same criteria multiple times?

SELECT * FROM tblEmployees E
WHERE E.CurrentAddress LIKE '%dan%' OR 
E.Email1 LIKE '%dan%' OR
E.Email2 LIKE '%dan%' OR 
E.LatinName LIKE '%dan%'
Bong Daleap
  • 3
  • 1

3 Answers3

1

There are other ways, but yours is probably the most efficient already. You could always do something like:

SELECT *
FROM tblEmployees
WHERE CurrentAddress + Email1 + Email2 + LatinName LIKE '%dan%'

If some of the columns are NULL, you could use ISNULL([field], '').

However, as @MitchWheat pointed out, it's not exactly the same query, since a field could end
by d and the next field could start by an.

plalx
  • 42,889
  • 6
  • 74
  • 90
  • Thanks. what if I want to use all the columns in tblEmployees? – Bong Daleap Aug 28 '13 at 02:48
  • @BongDaleap Well, if you are not willing to hard-code all the columns, you could always build a dynamic SQL statement by fetching the column names http://stackoverflow.com/questions/600446/sql-server-how-do-you-return-the-column-names-from-a-table – plalx Aug 28 '13 at 02:58
  • 1
    that's not the same query: CurrentAddress ends in 'd', email1 starts with 'an' for example – Mitch Wheat Aug 28 '13 at 02:59
0

If none of the values are NULL, you could do:

SELECT *
FROM tblEmployees E
WHERE concat(E.CurrentAddress, ' ', E.email1, ' ', E.email2, ' ', E.LatinName) LIKE '%dan%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Agree with plalx on this one, but if you're worried about typing '%dan%' multiple times, use a variable:

BEGIN
    DECLARE @p_search NVARCHAR(50)
    SET @p_search = '%dan%'

    SELECT *
    FROM tblEmployees E
    WHERE E.CurrentAddress LIKE @p_search
        OR E.Email1 LIKE @p_search
        OR E.Email2 LIKE @p_search
        OR E.LatinName LIKE @p_search
END
GO
SpudmashCode
  • 1