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

Question

I need to filter out records based on some text matching in nvarchar(1000) column. Table has more than 400 thousands records 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?

You **do know** that using such a `LIKE '%A1%'` operation will definitely disable any index use and thus result in a **guaranteed full table scan** every time... — marc_s, Jan 31 '11 at 21:30

score 8 · Answer 1 · answered Jan 31 '11 at 20:40

8

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

answered Jan 31 '11 at 20:40

John Hartsock

85,422
23
131
146

6

I have a feeling you've taken the sample code given a bit too literally. – Joe Stefanelli Jan 31 '11 at 20:49
@Joe Stefanelli ... Perhaps I have but all OP mentioned was a "preferred workaround" This is clearly one way. If it was performance then FullText Indicies could be useful. – John Hartsock Jan 31 '11 at 20:52

score 6 · Answer 2 · edited Oct 20 '12 at 07:08

6

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')

edited Oct 20 '12 at 07:08

xdazz

158,678
38
247
274

answered Oct 20 '12 at 06:30

Nusret Zaman

61
1
1

After some research, LIKE() seems to be better performing than SUBSTRING(). – brandeded Nov 23 '12 at 18:06

score 4 · Answer 3 · answered Jan 31 '11 at 20:42

4

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%'

answered Jan 31 '11 at 20:42

msms

172
6

5

I have a feeling you've taken the sample code given a bit too literally. – Joe Stefanelli Jan 31 '11 at 20:48
1

Not really. The provided query would just do the same thing as the original. The MSDN link explains why. – msms Jan 31 '11 at 20:50
1

I could be wrong, but I suspect A1, B1, etc. are just dummy placeholders for the real strings being searched for. – Joe Stefanelli Jan 31 '11 at 20:53
1

@Joe: So? If it doesn't make sense to @Jango I would expect a comment from him/her. – msms Jan 31 '11 at 20:59

score 0 · Answer 4 · edited May 23 '17 at 12:01

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.

score 0 · Answer 5 · answered Jan 31 '11 at 20:39

0

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.

answered Jan 31 '11 at 20:39

Lamak

69,480
12
108
116

Keep in mind that fulltext indexing is word based while LIKE is searching for a character pattern within a string. As a concrete example, a fulltext search for 'work' would not find the word in 'overworked', but a LIKE clause will. – Joe Stefanelli Jan 31 '11 at 20:45

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

5 Answers5

Linked