Search code examples
sqlstringhivehiveqlgreenplum

Hive Substring from complex subquery

As below substring is developed by Greenplum platform, we have to migrate similar operation to Hive supported. kindly help us.

select substring(a.dealer_msisdn from char_length(a.dealer_msisdn)-9) as dealer_msisdn

example of msisdn value with above query for greenplum

select substring('9970050916' from char_length('9970050916')-9) as dealer_msisdn

Please help me similar operation needs to migrate hive.

Solution

  • In hive substring function syntax is different: substr(string|binary A, int start, int len), len is optional parameter.

    Try this:

    select substr('9970050916', char_length('9970050916')-9)

    Read Hive UDFs manual