11

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?

Improve this question
1
  • 4
    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... Commented Jan 31, 2011 at 21:30

5 Answers 5

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have a feeling you've taken the sample code given a bit too literally.
@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.
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')
Improve this answer

1 Comment

After some research, LIKE() seems to be better performing than SUBSTRING().
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%'
Improve this answer

4 Comments

I have a feeling you've taken the sample code given a bit too literally.
Not really. The provided query would just do the same thing as the original. The MSDN link explains why.
I could be wrong, but I suspect A1, B1, etc. are just dummy placeholders for the real strings being searched for.
@Joe: So? If it doesn't make sense to @Jango I would expect a comment from him/her.
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.

Improve this answer

1 Comment

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.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.