SQL Statement - CHARINDEX Invalid Function-CodePudding

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?

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

CodePudding user response：

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)