How to use like condition with multiple values in sql server 2005?

I need to filter out records based on some text matching in nvarchar(1000) column. Table has more than 400 thousands reco开发者_JAVA技巧rds and growing. For now, I am using Like condition:-

SELECT 
    *
FROM
    table_01
WHERE
    Text like '%A1%'
    OR Text like '%B1%'
    OR Text like '%C1%'
    OR Text like '%D1%'

Is there any preferred work around?

SELECT 
    *
FROM
    table_01
WHERE
    Text like '%[A-Z]1%'

This will check if the texts contains A1, B1, C1, D1, ...

Reference to using the Like Condition in SQL Server

You can try the following if you know the exact position of your sub string:

SELECT 
    *
FROM
    table_01
WHERE
    SUBSTRING(Text,1,2) in ('B1','C1','D1')

Have a look at LIKE on msdn.

You could reduce the number filters by combining more details into a single LIKE clause.

SELECT 
    *
FROM
    table_01
WHERE
    Text like '%[ABCD]1%'

If you can create a FULLTEXT INDEX on that column of your table (that assumes a lot of research on performance and space), then you are probably going to see a big improvement on performance on text matching. You can go to this link to see what FULLTEXT SEARCH is and this link to see how to create a FULLTEXT INDEX.

I needed to do this so that I could allow two different databases in a filter for the DatabaseName column in an SQL Server Profiler Trace Template.

All you can do is fill in the body of a Like clause.

Using the reference in John Hartscock's answer, I found out that the like clause uses a sort of limited regex pattern.

For the OP's scenario, MSMS has the solution.

Assuming I want databases ABCOne, ABCTwo, and ABCThree, I come up with what is essentially independent whitelists for each character:

Like ABC[OTT][NWH][EOR]%

Which is easily extensible to any set of strings. It won't be ironclad, that last pattern would also match ABCOwe, ABCTnr, or ABCOneHippotamus, but if you're filtering a limited set of possible values there's a good chance you can make it work.

You could alternatively use the [^] operator to present a blacklist of unacceptable characters.