Redshift SQL REGEXP_REPLACE function

Question

I have a value which is duplicated from source (can't do anything about that). I have read some examples here https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html

Example value: ABC$ABC$ So just trimming anything after the first '€'. I tried this, but I cannot figure out the correct REGEX expression.

REGEXP_REPLACE(value, '€.*\\.$', '')

Tim Biegeleisen · Accepted Answer · 2021-08-02 09:11:52Z

2

Your current regex pattern is including a dot as the final character. Remove it and your approach should work:

SELECT REGEXP_REPLACE(value, '€.*$', '') AS value_out
FROM yourTable;

answered Aug 2, 2021 at 9:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Caius Jard · Accepted Answer · 2021-08-02 09:23:10Z

2

So just trimming anything after the first '€'.

Why use regex at all? Why not just..

SELECT LEFT(value, CHARINDEX('€', value)-1)

If not all your data has a euro sign, consider WHERE value like '%€%'

answered Aug 2, 2021 at 9:09

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Gordon Linoff · Accepted Answer · 2021-08-02 10:44:39Z

1

Or you can take the initial sequence of non-€ characters:

REGEXP_SUBSTR(value, '^[^€]+')

answered Aug 2, 2021 at 10:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges