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

Remove space between number and character – PostgreSQL/REGEXP_REPLACE

I have a table with medication_product_amount column where there are spaces between numbers and characteres like below:

medication_product_amount
1 UN DE 50 ML
20 UN
1 UN DE 600 G

What I want is to remove the single space ONLY between numbers and characters, something like this:

new_medication_product_amount
1UN DE 50ML
20UN
1UN DE 600G

To do this, I am looking for a regular expression to use in the function REGEXP_REPLACE. I tried using the pattern below, indicating to replace the single space after the numbers, but the output remained the same as the input:

select REGEXP_REPLACE(medication_product_amount, '(^[0-9])( )', '1') as new_medication_product_amount
from medications

Can anyone help me come up with the right way to do this? Thanks!

Tags:

2

Answers


  1. 0

    Your regex is a little off. First what yours does. '(^[0-9])( )', '1')

    • (^[0-9]) Start Capture (field 1) at the beginning of the string for 1 digit
    • followed by Start Capture (field 2) for 1 space.
    • Replace the string by field1.

    The problems and correction:

    • What you want to capture does not necessary the first character of the string. So eliminate the anchor ^.
    • What you want to capture may be more that 1 digit in length. So replace [0-9] by [0-9]+. I.E any number of digits.
    • Not actually a problem but a space holds no special meaning in a regexp, it is just a space so no need to capture it unless user later. Replace ( ) with just .
    • END of Pattern. But there may be other occurrences. Tell Postgres to continue with the above pattern until end of string. (see flag ‘g’).

    Resulting Expression/Query: (demo here)

    select regexp_replace(medication_product_posology, '([0-9]+) ', '1','g') as new_medication_product_posology
from medications;
    Login or Signup to reply.
  2. 0

    Match "digit space letter", capturing and the digit and letter using '([0-9]) ([A-Z])', then put them back using back references.

    select REGEXP_REPLACE(medication_product_amount, '([0-9]) ([A-Z])', '12') as new_medication_product_amount
from medications
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search