CODEWITHSUNDEEP
sqlregexamazon-redshift
Dametime
Dametime

Reputation: 723

Redshift SQL REGEXP_REPLACE function

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, '€.*\\.$', '')

Upvotes: 0

Views: 1769

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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

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

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

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 '%€%'

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

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;

Upvotes: 2

Related Questions