8

In the ETL process, I receive a varchar field, and the length (of the value) is changed from row to row. I need to keep 5 symbols from the right side of the string. It means that I need to cut the left side but I can't, due to the unknown length.

I've tried the select substring('24:15:11',4, 5), but it doesn't help me, the string could be '2019-05-01 22:15:11'.

sql:

select substring('24:15:11',4, 5)

expected:

15:11
René Vogt
  • 43,056
  • 14
  • 77
  • 99
Terpsihora
  • 83
  • 1
  • 1
  • 3
  • Hi! Have you tried the function RIGHT() ? not sure if it exists in your exact flavour of sql but I use it all the time in sql server. – David Söderlund May 16 '19 at 07:31
  • 1
    @DavidSöderlund Is there a right() function in presto? I don't think so. – forpas May 16 '19 at 07:32
  • @DavidSöderlund - There doesn't appear to be a `RIGHT` function (https://prestodb.github.io/docs/current/functions/string), hence `SUBSTRING`. – Paul May 16 '19 at 07:34
  • I take it that your time segment *will* always be the same length (i.e. properly padded with lead zeroes (00:00:00)? – Paul May 16 '19 at 07:35

2 Answers2

11

You can use substr. Negative starting position is interpreted as being relative to the end of the string.

select substr('24:15:11', -5)
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
hotfix
  • 3,376
  • 20
  • 36
4

You can use length() to determine the 2nd argument of substr():

select substr('24:15:11', length('24:15:11') - 4, 5)

or simply:

select substr('24:15:11', length('24:15:11') - 4)

Read about preosto's string functions.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Cool Thanks! didn't know that I can use negative index – Terpsihora May 16 '19 at 07:44
  • @Terpsihora - he's not, really, he's deducting from the length. The second version is taking the length - 4 to the end of the string (i.e. there's no argument specified for number of characters, so EoS is assumed). – Paul May 16 '19 at 08:02
  • Well, linked docs for `substr` says *A negative starting position is interpreted as being relative to the end of the string.* - what's the point of using `length` then? – barbsan May 16 '19 at 08:42