CODEWITHSUNDEEP
sqlamazon-athenaprestotrino
user1179317
user1179317

Reputation: 2903

AWS Athena: Right function

I would like to use the function right from AWS athena, but it does not seem to be supported.

How would I go about and trimming certain characters in Athena?

For example I would like to do RIGHT('1313521521', 4)

to get 1521. Unfortunately I would get something like

Queries of this type are not supported

Upvotes: 1

Views: 7524

Answers (2)

Michael Alperovich
Michael Alperovich

Reputation: 11

A negative number: substring(str, -4) would work as well as a substitute for RIGHT(str, 4)

from Athena documentation: A negative starting position is interpreted as being relative to the end of the string.

Upvotes: 1

Guru Stron
Guru Stron

Reputation: 141665

Athena uses Presto as SQL engine and it does not have right function, but you can mimic it using substr and determining the staring position greatest(length(str) - 3, 1) - we need to start from 4th from last index, if string is too short - start from 1st index, cause Presto indexes starting from 1):

--sample data
with dataset(str) as (
    VALUES ('id1'),
    ('1313521521'),
    ('')
)

-- query
select substr(str, greatest(length(str) - 3, 1))
from dataset

Output:

_col0
id1
1521
 

Upvotes: 2

Related Questions