2 réponses
- Le plus récent
- Le plus de votes
- La plupart des commentaires
1
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;
0
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
Contenus pertinents
- Réponse acceptéedemandé il y a un an
- demandé il y a un an
- AWS OFFICIELA mis à jour il y a 2 ans