Filter condition in Redshift-CodePudding

I am fairly new to redshift and I have the following postcodes in my table

B13 7GB
BA43 87F
BR8 H4D
B4H HFT

I would like to only extract the rows where there is a number FROM 0-9 after the first letter.

Expected output

B13 7GB
B4H HFT

Thank you

CodePudding user response：

Perhaps someone can provide an "simpler" answer, but I like using REGEX functions to be as precise as possible.

select code
from tbl
where regexp_count(code,'^[A-Z]{1}[0-9]{1}')>0

^ -> Only check the start of the string (our code).

[A-Z]{1} -> Search for ONE Capital Letter.

[0-9]{1} -> Search for ONE number from 0-9.

All together:

At the start of the string, search for ONE capital letter that is followed by ONE number from 0-9.

CodePudding user response：

Regexp is very flexible but in Redshift it is not very fast. If you dataset is very large you will likely be better off for this simple case with SIMILAR TO.

select *
from tbl 
where code SIMILAR TO '_[0-9]%';

LIKE and SIMILAR TO are less flexible than regexp but compile and run faster. In general the more flexible the syntax the harder it is to execute. This (and backward compatibility) is why LIKE and SIMILAR TO still exists.