CODEWITHSUNDEEP
sqlregexamazon-redshift
Arturo Sbr
Arturo Sbr

Reputation: 6333

Trim string after first occurrence of a character

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:

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.

Upvotes: 0

Views: 783

Answers (2)

dougp
dougp

Reputation: 3087

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.

Upvotes: 2

The fourth bird
The fourth bird

Reputation: 163352

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'

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

Upvotes: 2

Related Questions