How to get substring based on a character and starting to read the string from the right

Question

I have the following values on a column:

DB3-0800-VRET,
DB3-0800-IC,
IB-TZ-850-IB,
O11FS-OB ...

From each value I want to remove the last part after the dash. I need to have the following result:

DB3-0800-VRET -> DB3-0800,
DB3-0800-IC   -> DB3-0800,
O11FS-OB      -> O11FS

I tried to work with the SPLIT_PART function of RedShift but I didn't have any luck. If someone knows a regex to select the part I need I'd be grateful.

Redshift or Postgres? Those are two very different DBMS (even though Redshift is based on Postgres) — user330315
– user330315, Commented Jun 17, 2019 at 11:09
@a_horse_with_no_name it's Redshift, but since I know Redshift is based on Postgres I also included it in a tag. — Plarent Haxhidauti
– Plarent Haxhidauti, Commented Jun 17, 2019 at 11:36

Gordon Linoff · Accepted Answer · 2019-06-17 11:07:45Z

2

In both Postgres and Redshift, you should be able to use regexp_replace():

select regexp_replace(str, '-[^-]+$', '')

answered Jun 17, 2019 at 11:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Plarent Haxhidauti Over a year ago

Thank you for the help. In redshift this query is not valid. When I remove the last quotation marks, I only get the part I want to remove. Can you let me know who can I select the substring without including the part I'm getting in return now.

Gordon Linoff Over a year ago

@PlarentHaxhidauti . . . It should work: docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html.

Plarent Haxhidauti Over a year ago

Thank you I made a spelling mistake in my query. It's working:))

Collectives™ on Stack Overflow

How to get substring based on a character and starting to read the string from the right

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related