RIGHT() / LEFT() functions

Question

Is there a function in Hiveql that is equivalent to Right() or Left() function from TSQL? For example, RIGHT(col1,10) to get the first 10 characters from col1.

sandeep rawat · Accepted Answer · 2022-02-21T04:02:36.260

37

There is no right or left function, but you can implement the same functionality with substr, like this:

left(column, nchar) = substr(column, 1* nchar)

right(column, nchar) = substr(column, (-1)* nchar)

Here nchar is number of characters.

edited Feb 21 '22 at 04:02

answered Jan 03 '17 at 03:30

sandeep rawat

4,797
1
18
36

Can `(-1)*` be changed to `-1,`? It is a typo? – Feb 21 '22 at 02:45
I found I had to use `SUBSTR(column, start, num_chars)`as per docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions eg. `substr(column, 0, 3)` gives the first 3 chars in the a string. – silverdagger Aug 10 '22 at 07:28

score 9 · Answer 2 · edited Feb 21 '22 at 02:45

9

This works: substr (col, -nchar) = right(col, nchar).

hive> select substr('adbcefghij',-4);
ghij
Time taken: 40.839 seconds, Fetched: 1 row(s)

edited Feb 21 '22 at 02:45

answered Dec 19 '17 at 14:33

jose_bacoy

12,227
1
20
38

For the win! I've proposed an update to the WRONG 'accepted' answer to use this formula, which is also simpler than ALEKSEY NIKITOV's (which is also correct.) Thanks anonyXmous! – Ward W Feb 06 '18 at 18:42

score 6 · Answer 3 · edited Feb 21 '22 at 02:45

6

right(column, nchar) = substr(column, (length(column)-nchar+1), nchar)

edited Feb 21 '22 at 02:45

answered Oct 26 '17 at 17:34

ALEKSEY NIKITOV

61
1
1

This is almost the same as the answer given 10 months ago. – Alejandro Oct 26 '17 at 17:52
1

Almost the same, except this one is correct and the other was incorrect. – Brian Morearty Nov 21 '17 at 22:51
1

@BrianMorearty Then the accepted answer should be edited – zthomas.nc Nov 30 '17 at 19:53

RIGHT() / LEFT() functions

3 Answers3