CODEWITHSUNDEEP
sqlamazon-redshift
user14263992
user14263992

Reputation: 115

Multiple values inside SQL LIKE

name 12ab_cd34 56ef_gh78 12ef_gh34

This is working:

SELECT * FROM table WHERE name LIKE '%ab_cd%'

How to include '%ef_gh%' in a single/multiple statement?

My try:

SELECT * FROM table WHERE name LIKE IN ('%ab_cd_%', '%ef_gh%')

Upvotes: 3

Views: 10083

Answers (1)

alessiosavi
alessiosavi

Reputation: 3037

The SIMILAR TO operator matches a string expression, such as a column name, with a SQL standard regular expression pattern. A SQL regular expression pattern can include a set of pattern-matching metacharacters, including the two supported by the LIKE operator.

The SIMILAR TO operator returns true only if its pattern matches the entire string, unlike POSIX regular expression behavior, where the pattern can match any portion of the string.

SIMILAR TO performs a case-sensitive match.

NOTE:
Regular expression matching using SIMILAR TO is computationally expensive. We recommend using LIKE whenever possible, especially when processing a very large number of rows. For example, the following queries are functionally identical, but the query that uses LIKE runs several times faster than the query that uses a regular expression:

select count(*) from event where eventname SIMILAR TO '%(Ring|Die)%'; 
select count(*) from event where eventname LIKE '%Ring%' OR eventname LIKE '%Die%';

Upvotes: 5

Related Questions