Regex to match alphanumeric -- query rows that contain numbers in postgresql

Question

I want to write a regex that matches string that has both numeric and alphabets. ^[a-zA-Z0-9]*$ -- this pattern returns numeric as well as alphabets, but I want only alphanumeric. Basically I'm using postgresql to query rows that contain numericals in it.

Can you give examples of what you want to match and not match? — Gordon Linoff
– Gordon Linoff, Commented Aug 14, 2016 at 18:07
Always only alpha numeric but never foreign characters or spaces? — Phill
– Phill, Commented Aug 15, 2016 at 9:32
Related: stackoverflow.com/questions/59309953/… contains solution with ~ '[[:alpha:]]' for Unicode codepoints. — Code4R7
– Code4R7, Commented Sep 24 at 5:25

Gordon Linoff · Accepted Answer · 2019-11-27 16:04:54Z

7

I think multiple regex's is the easiest way:

where col ~ '^[a-zA-Z0-9]*' and
      col ~ '[0-9]' and
      col ~ '[a-zA-Z]'

There is probably a complicated regexp that combines this all together, but this seems like the most intuitive method.

edited Nov 27, 2019 at 16:04

answered Aug 14, 2016 at 18:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Martin Brandl · Accepted Answer · 2016-08-14 18:04:48Z

1

This should work:

(?:[a-zA-Z]\d)|(?:\d[a-zA-Z])

Regular expression visualization

answered Aug 14, 2016 at 18:04

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

1 Comment

Phill Over a year ago

This will match a string that contains any other character also, spaces and special characters. All you're validating is that it contains #Char or Char# is contained in the column.

Collectives™ on Stack Overflow

Regex to match alphanumeric -- query rows that contain numbers in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related