PostgreSQL - regexp_replace on SELECT

Question

PostgreSQL - regexp_replace on SELECT

Navigation

#1 by (1 votes)

0

I am working with PostgreSQL and I can not perform a replacement query with REGEXP_REPLACE . What I need is for the column to go from what it looks like on the left side to what it appears on the right side:

-------------             -------------
-- original               -- modificado
-------------             -------------
142/16                    142
145/16                    145
146/16                    146
147/16                    147
16/F/2016                 16
213/G/2016                213
233-M-2015                233
24/G/2016                 24
24/S/2016                 24
25/L/2016                 25
269/S/2016                269
28/S/2016                 28

I need to only keep the value before '/' or '-'.

I've tried things like:

SELECT
  columna                                    AS original,
  REGEXP_REPLACE(columna, '/(\w)*(\d)*', '') AS modificado
FROM tabla;

SELECT
  columna                                             AS original,
  REGEXP_REPLACE(columna, '^/[a-zA-Y0-9 ]', '', 'gi') AS modificado
FROM tabla;

SELECT
  columna                                            AS original,
  REGEXP_REPLACE(columna, '(/[\d*+])(\w)', '', 'gi') AS modificado
FROM tabla;

... but none throws me what I need.

Someone who can guide me or give me a hand. Thanks from now.

sql postgresql

asked by мαяςєlσ 10.10.2018 в 14:25

source

1 answer

Problems with my ShowDialog vb.NET form Change color to an item in the Canvas

score 1 · Answer 1

After struggling for a long time with regular expressions, I found the solution that at least up to now I find. I do not know if it is the most optimal but I leave it below:

SELECT
  columna                                                                  AS original,
  REGEXP_REPLACE(columna, '((/|-)+[[:alpha:]]*)+([[:digit:]]*)', '', 'gi') AS modificado
FROM tabla;

If someone has a better way, please post it.