Geek Answers Handbook

The fastest way to check if any case of a pattern exists in a column using SQL

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

What I'm doing now is something like

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

and see if the counter is larger.

I am curious to know if there is a way to speed up this process, since this type of pattern search happens in a loop in my query, and all I need to know is if there is even one such case, and not the total number of cases.

Any suggestions would be appreciated.

EDIT: I run this inside a Teradata stored procedure to check data quality.

+6
source share
3 answers

If you don’t need an actual account, the most efficient way in Teradata is to use EXISTS :

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

This will return an empty result set if the template does not exist.

+2
source

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

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

This is faster, because when it finds one match, it can return the result.

+3
source

In terms of performance, the best approach is:

  • select a result set based on your template;
  • limit the size of the result set to 1.
  • Check if the result was returned.

Doing this does not allow the database engine to perform 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 are using. In MySQL, it looks something like this:

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

In Oracle, it will look like this:

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

More articles:


All Articles