PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Multiple regex replaces for 1 column

I have a case I need to replace multiple substrings within 1 column using PostgreSQL.

Example:

Region City
Moscow obl Istra
Rep Bashkortostan Ufa
Udmurtskaya Rep Izhevsk

I need all substrings ‘obl’ to be replaced with ‘oblast’ and ‘Rep’ replaced with ‘Republic’

I tried to use subquery like

select regexp_replace
                     ((select regexp_replace(
                                             wt.region_with_type,
                                             'sobl$','oblast') 
                                             from work_table wt),        
                      '^Reps|sRep$','Republic') as project_f_region
                       from work_table wt

But it throws an error

postgresql error: more than one row returned by a subquery used as an expression

I tried to google the answer, but couldn’t find any solutions.
I know I can use CTE, but as for me the query gets too bulky

Is there any other way to use multiple replaces with different substrings?

Tags:

3

Answers


  1. 0

    I suggest using CASE.

    select
case
  when regexp_like(wt.region_with_type, 'sobl$') then regexp_replace(wt.region_with_type,'sobl$','oblast')
  when regexp_like(wt.region_with_type, '^Reps|sRep$') then regexp_replace(wt.region_with_type,'^Reps|sRep$','Republic')
  else wt.region_with_type
end as substitution
from work_table wt
    Login or Signup to reply.
  2. 0

    You can use multiple regex_replace functions :

    SELECT regexp_replace(
          regexp_replace(
              wt.region_with_type, 
              'sobl$', 
              'oblast'
          ), 
          '^Reps|sRep$', 
          'Republic'
      ) AS project_f_region
FROM work_table wt
    Login or Signup to reply.
  3. 0

    You can nest function calls directly, without mixing in subqueries. Simply

    select f(f(x)) from work_table;

    in contrast to your

    select f(select f(x) from work_table);

    which would only work if you had a single row in your work_table. I’m wondering if it wasn’t just a typo, a lost or misplaced closing parenthesis ) and you actually tried

    select f(select f(x)) from work_table;

    Or, in full:

    select regexp_replace(
           (select regexp_replace( wt.region_with_type
                                  ,'sobl$'
                                  ,'oblast'
                   )
           )--this ends the inner select that doesn't have a `from`, just a single value
          ,'^Reps|sRep$'
          ,'Republic'
       )
from work_table wt;

    Which technically uses an entirely correct scalar subquery, but that is just syntax noise you don’t really need. It would have the exact same result as

    select regexp_replace(
           regexp_replace( wt.region_with_type
                          ,'sobl$'
                          ,'oblast'
           )
          ,'^Reps|sRep$'
          ,'Republic'
       )
from work_table wt;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search