sqlhiveinstr

SQL Statement - CHARINDEX Invalid Function

I want to split a string and get the value after the space and am using below query. However i am getting an error that CHARINDEX is not valid. Are there any ways i can get around this?enter image description here

SELECT  productname,
SUBSTRING(productname, instr(' ', productname) +9, 50) AS ShortProductName       
FROM   ar_cem_financedb_analytics_finance.dimproduct
Solution

  • Hive does not support SQL Server's CHARINDEX() function.

    In your 2nd query you are using INSTR() but the arguments are reversed.
    Change to:

    SUBSTRING(productname, instr(productname, ' ') +9, 50)

    or use LOCATE():

    SUBSTRING(productname, locate(' ', productname) +9, 50)