Postgres SQL WHERE clause with 'OR' in pattern match

Question

I have strings of 010xxx, 011xxx, 110xxx, 111xxx, Q10xxx, Q11xxx in a field along with other values that are not similar. They might be XyzABC.

I have two regex patterns that separately give results that are good: [1Q]_[0-9]% and 0_[1-9]%

In words return true if

first letter is 1 or Q and the 3rd letter is a 0-9 

OR 

the first letter is 0 and the third letter is 1-9

How do I create a search pattern that does the OR either using SIMILAR TO or regex?

One version that works by itself is:

SELECT field FROM db WHERE field SIMILAR TO '[1Q]_[0-9]%'

Not wedded to SIMILAR or regex. They were just what I could get working until I tried to or them. Open to other suggestions.

Wiktor Stribiżew · Accepted Answer · 2020-11-09 08:20:51Z

3

You can use a SIMILAR TO pattern like

WHERE field SIMILAR TO '([1Q]_[0-9]|0_[1-9])%'

The SIMILAR TO pattern requires a full string match, so the pattern means: start with 1 or Q, then any char, then any digit, or start with 0, any char and a non-zero digit, and then there can be any 0 or more chars afterwards.

You can also use a regex like

WHERE field ~ '^(?:[1Q].[0-9]|0.[1-9])'

See the regex demo

Details:

^ - start of string
(?: - start of a non-capturing group:
- [1Q].[0-9] - 1 or Q, any char and a digit
| - or
- 0.[1-9] - 0, any char and a non-zero digit
) - end of a non-capturing group.

edited Nov 9, 2020 at 8:20

answered Nov 9, 2020 at 8:15

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

SIMILAR TO is internally converted to ~ automatically, so both versions are equally good.

Rud48 Over a year ago

Use the SIMILAR TO version and it worked. My problem was making the patter "readable" by putting spaces around the |. I was using regexplanet.com/advanced/postgresql/index.html for testing but will keep the other site in mind. Thanks. Up voted and marked as answer.

Collectives™ on Stack Overflow

Postgres SQL WHERE clause with 'OR' in pattern match

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related