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.

Answer 1

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.

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