Postgres regex string matching

Question

How would I find address records the do not contain a digit followed by a space? (eg., 15SMith St. as opposed to 15 Smith St)

WHERE a.address not like '.[0-9] .'

is clearly not working for me.

What about WHERE a.address NOT SIMILAR TO '%[0-9] %'?

Wiktor Stribiżew
– Wiktor Stribiżew

2017-02-08 20:44:03 +00:00
Commented Feb 8, 2017 at 20:44 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 8, 2017 at 20:44
Perfect! Thank You!

Tony Hendrix
– Tony Hendrix

2017-02-08 20:56:44 +00:00
Commented Feb 8, 2017 at 20:56 — Tony Hendrix
– Tony Hendrix, Commented Feb 8, 2017 at 20:56

Wiktor Stribiżew · Accepted Answer · 2017-02-08 21:02:46Z

2

You may use SIMILAR TO with NOT and a pattern that is a "a curious cross between LIKE notation and common regular expression notation". It supports [0-9]:

A bracket expression [...] specifies a character class, just as in POSIX regular expressions.

So, you may use

WHERE a.address NOT SIMILAR TO '%[0-9] %'

Here, % matches any number of any chars, [0-9] matches any digits, a space matches a space and then % again matches any number of any chars. These % are necessary because SIMILAR TO requires a full string match, same as LIKE.

answered Feb 8, 2017 at 21:02

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Postgres regex string matching

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related