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

Remove whitespaces after number – PostgreSQL

Basically, I want to remove the whitespace that exists after numbers

Input:

medication_title

CLORIDRATO DE VENLAFAXINA 75 MG
VIIBRYD 40 MG
KTRIZ UNO 0.6 U/G

Ouput:

medication_title                     medication_title2

CLORIDRATO DE VENLAFAXINA 75 MG     CLORIDRATO DE VENLAFAXINA 75MG 
VIIBRYD 40 MG                       VIIBRYD 40MG
KTRIZ UNO 0.6 U/G                   KTRIZ UNO 0.6U/G

Ideas?

Tags:

2

Answers


  1. 0

    We can use a regex replacement here:

    SELECT
    medication_title,
    REGEXP_REPLACE(medication_title,
                   'y(d+(?:.d+)?) ([^[:space:]]*G)y',
                   '12') AS medication_title2
FROM yourTable;

    screen capture from demo link below

    Demo

    Here is an explanation of the regex pattern:

    • y word boundary
    • ( match and capture in 1
      • d+ a number
      • (?:.d+)? followed by optional decimal component
    • ) close capture group 1
    • match a space (the one we want to remove)
    • ( match and capture in 2
      • [^[:space:]]* zero or more leading non whitespace characters
      • G folllwed by "G"
    • ) close capture group 2
    • y another word boundary
    Login or Signup to reply.
  2. 0

    You can capture the sequences with a regular expression and then assemble them back as needed, as in regexp_replace(x, '([^0-9]*[0-9]) +([^0-9.]+)', '12').

    For example:

    select *, regexp_replace(x, '([^0-9]*[0-9]) +([^0-9.]+)', '12') as y
from (
  select 'CLORIDRATO DE VENLAFAXINA 75 MG'
  union all select 'VIIBRYD 40 MG'
  union all select 'KTRIZ UNO 0.6 U/G  '
) t (x)

    Result:

    x                                y                              
-------------------------------- ------------------------------ 
CLORIDRATO DE VENLAFAXINA 75 MG  CLORIDRATO DE VENLAFAXINA 75MG 
VIIBRYD 40 MG                    VIIBRYD 40MG                   
KTRIZ UNO 0.6 U/G                KTRIZ UNO 0.6U/G
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search