CODEWITHSUNDEEP
pythonregexpostgresqlsqlalchemy
Haris Muzaffar
Haris Muzaffar

Reputation: 441

How to merge multiple regex into one in SQLAlchemy?

I am trying to remove whitespaces from a column in a Postgres table.
I am using SQLAlchemy to do it.

e.g. ' some value ' should become 'some value'.

My code is:

sqlalchemy.func.regexp_replace(
                # replace whitespaces with a single space
                sqlalchemy.func.regexp_replace(source_column_instance, ' {2,}', ' ', 'g'),
                # also remove leading and trailing whitespaces
                '^ | $', '', 'g')

The above is working fine but I want to merge the two regexes into one.

Upvotes: 1

Views: 502

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627327

You may use

sqlalchemy.func.regexp_replace(source_column_instance, '^ +| +$|( ){2,}', '\\1', 'g')

Here,

  • ^ + - matches 1 or more spaces at the start of the string
  • | - or
  • +$- matches 1 or more spaces at the end of the string
  • | - or
  • ( ){2,} - matches and captures a single space into Group 1 two or more times.

The replacement is \1, backreference to Group 1 value, so that only one space is kept in the result where there were two or more spaces.

Upvotes: 2

Related Questions