CODEWITHSUNDEEP
regexpostgresql
Rud48
Rud48

Reputation: 1074

Postgres SQL WHERE clause with 'OR' in pattern match

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.

Upvotes: 2

Views: 498

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627537

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.

Upvotes: 3

Related Questions