Trim string after first occurrence of a character

Question

I'm working with a varchar column in AWS Redshift. Each string in it has at least one hyphen (-).

I want to keep the substring after the first hyphen. For example:

• 00-11-22-33 -> 11-22-33
• 00-112233 -> 112233

The solutions to this question do not work when there are multiple hyphens in a string.

For instance:

select
  split_part('00-11-22-33', '-', 2) as attempt1           -- returns '11'
  , regexp_replace( '00-11-22-33', '.*-', '') as attempt2 -- returns '33'
;

I'm looking for a solution that returns 11-22-33.

Answer 1

You could match until the first hyphen with ^[^-]*-

And replace with an empty string.

regexp_replace('00-11-22-33', '^[^-]*-', '');

If there should be at least a single char after the hyphen, then you can match with this pattern and replace with capture group 1 like '$1' instead of an empty string.

^[^-]*-(.+)

If the char after the hyphen should not be another hyphen, then you can match a single char other than a hyphen and also replace with '$1'

^[^-]*-([^-].*)

Answer 2

If there's always a hyphen, and you always want only what is after the first hyphen, you may be working too hard.

Here's a more brute force approach.

select substring(
  '00-11-22-33' 
  FROM charindex('-', '00-11-22-33') + 1)
)

or

select substring(
  '00-11-22-33', 
  charindex('-', '00-11-22-33') + 1, 
  len('00-11-22-33') - charindex('-', '00-11-22-33')
)

or

select substring(
  '00-11-22-33', 
  charindex('-', '00-11-22-33') + 1, 
  len('00-11-22-33')
)

...because it won't return more characters than exist.