Fastest way to check if any case of a pattern exist in a column using SQL

Question

I am trying to write code that allows me to check if there are any cases of a particular pattern inside a table.

The way I am currently doing is with something like

select count(*) 
from database.table 
where column like (some pattern)

and seeing if the count is greater than 0.

I am curious to see if there is any way I can speed up this process as this type of pattern finding happens in a loop in my query and all I need to know is if there is even one such case rather than the total number of cases.

Any suggestions will be appreciated.

EDIT: I am running this inside a Teradata stored procedure for the purpose of data quality validation.

How are you using this? A loop in a query is usually a bad sign... SQL works much better when you use set-based approaches rather than loops. More context would be useful in giving you a good answer. — Mike D., Oct 29 '15 at 04:18
Also specify your RDBMS while posting your question as differents DB have different fucntions. — anwaar_hell, Oct 29 '15 at 04:29
Thanks for everyone's comments. I should have explained it better. I am using these queries inside of a Teradata stored procedure and I am trying to use this to do a data quality check on our table. The basic concept is to have a loop with to go through the various columns in the table and doing the check using the select count statement to see if there are any cases that are outside of what we expect to see. I think using the IF Exist might work better as suggest below, but I think the normal case will be where there are no matches. Thanks. — Hangil Jang, Oct 29 '15 at 04:42

score 3 · Answer 1 · answered Oct 29 '15 at 04:24

Using EXISTS will be faster if you don't actually need to know how many matches there are. Something like this would work:

IF EXISTS (
    SELECT *
    FROM bigTbl
    WHERE label LIKE '%test%'
)
    SELECT 'match'
ELSE
    SELECT 'no match'

This is faster because once it finds a single match it can return a result.

dnoeth · Accepted Answer · 2015-10-29T21:19:22.867

2

If you don't need the actual count, the most efficient way in Teradata will use EXISTS:

select 1
where exists 
 ( select *
   from database.table 
   where column like (some pattern)
 )

This will return an empty result set if the pattern doesn't exist.

edited Oct 29 '15 at 21:19

answered Oct 29 '15 at 06:46

dnoeth

59,503
4
39
56

Actually I am getting SELECT Failed. 3706: Syntax error: expected something between ')' and '*'. when running this query. – Hangil Jang Oct 29 '15 at 15:59
@HangilJang: Of course this fails, there should be no `*`, I'll fix my answer – dnoeth Oct 29 '15 at 21:19

Robby Cornelissen · Answer 3 · 2015-10-29T04:24:34.327

In terms of performance, a better approach is to:

select the result set based on your pattern;
limit the result set's size to 1.
Check whether a result was returned.

Doing this prevents the database engine from having to do a full table scan, and the query will return as soon as the first matching record is encountered.

The actual query depends on the database you're using. In MySQL, it would look something like:

SELECT id FROM database.table WHERE column LIKE '%some pattern%' LIMIT 1;

In Oracle it would look like this:

SELECT id FROM database.table WHERE column LIKE '%some pattern%' AND ROWNUM = 1;

Fastest way to check if any case of a pattern exist in a column using SQL

3 Answers3