redshift rtrim and collation

Question

In redshift, how do I use rtrim to get the original string with its original casing, but match the string to remove case insenstive?

These aren't acceptable solutions:

```
rtrim('aBcd', 'Cd') = aBc
rtrim('aBcd', 'cd') = aB
rtrim(lower('aBcd'), 'cd) = ab

```

And this doesn't work

```
rtrim(collate('aBcd', 'case_insensitive), collate('Cd', 'case_insensitive')) != aB
```

Do I have to use substring instead?

Answer

The solution is to use a combination of upper, position, substring, and case.

```
create temporary table mytable (mystring varchar(100));
insert into mytable values ('aBcd'), ('aBCd'), ('aBCD'), ('aBcD');
select substring(mystring, 1, position('CD' in upper(mystring)) - 1) 
from mytable;
 substring 
-----------
 aB
 aB
 aB
 aB
```

If your data has some rows without some form of CD at the end of it, you need to add some additional logic.

```
insert into mytable values ('xy'), ('Xy'), ('xY'), ('XY');
select substring(mystring, 1, case when position('CD' in upper(mystring)) = 0 then length(mystring) else position('CD' in upper(mystring)) - 1 end) 
from mytable;
```

Answer

Hi,

I would use the regexp_substr function, it provides several options with less code https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_SUBSTR.html.

Example: select regexp_substr('aBcd','aB');

Regards

redshift rtrim and collation

Relevant content