How to merge multiple regex into one in SQLAlchemy?

Question

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.

Wiktor Stribiżew · Accepted Answer · 2019-10-16 12:09:18Z

2

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.

edited Oct 16, 2019 at 12:09

answered Oct 16, 2019 at 11:04

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

5 Comments

Haris Muzaffar Over a year ago

it actually works. Thanks a lot. However, it will take me few more minutes to understand the backreference as I have seen it the first time today

Wiktor Stribiżew Over a year ago

@HarisMuzaffar See Repeating a Capturing Group vs. Capturing a Repeated Group

Haris Muzaffar Over a year ago

Hey, it doesn't work if i have more than one leading space. Can you test that?

Wiktor Stribiżew Over a year ago

@HarisMuzaffar Did you use '^ +| +$|( ){2,}'?

Haris Muzaffar Over a year ago

this one works, the first one i.e. '^ | $|( ){2,}' seems to be not working. May be you should try to modify '^ | $|( ){2,}' or remove it from your answer

Collectives™ on Stack Overflow

How to merge multiple regex into one in SQLAlchemy?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related