SQL Statement - CHARINDEX Invalid Function

Question

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

It depends on the DBMS you're using. Is it SQL Server? – lemon Aug 08 '22 at 10:18 — lemon, Aug 08 '22 at 10:18

score 2 · Accepted Answer · answered Aug 08 '22 at 10:24

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)

SQL Statement - CHARINDEX Invalid Function

1 Answers1