my string is morning is here sunshine is here the sky is clear the morning is here.

I'm doing an rtrim and ltrim function on the string morning is here:

ltrim('morning is here sunshine is here the sky is clear the morning is here', 'morning is here')
rtrim('morning is here sunshine is here the sky is clear the morning is here', 'morning is here')

my output is:

ltrim -- unshine is here the sky is clear the morning is here 
rtrim -- morning is here sunshine is here the sky is clea

I don't get why the extra character is trimmed off?

CodePudding user response：

I suspect that you think the RTRIM function takes a whole string from the end and removes it. However, that is not how the function works.

From RTRIM function - Amazon Redshift:

The RTRIM function trims a specified set of characters from the end of a string.

If any of the supplied characters are at the end of the string, then it is removed. This continues until the last character is not any of the providing characters.

For example:

rtrim('morning is here sunshine is here the sky is clear the morning is here', 'eghirs ')

would remove any of the provided characters from the end of the string, giving:

morning is here sunshine is here the sky is clear the mornin

It looks at the characters, not the string as a whole.

In your RTRIM example, it also removed the space and r because those characters were included in your second argument.

If you are wondering why the command behaves this way, it is intended to be used to 'trim' unwanted characters such as spaces, tabs, quotes and punctuation. It is not intended to be used for removal of complete strings.

If you want to remove a specific string from the end you could use something like:

case when substring(column, len(column)-15, 15) = 'morning is here'
     then substring(column, len(column)-15
     else column
     end