SQL charindex function?

[Barb]

Hi all,

I need to use the SQL charindex function, but I'm getting a databricks error that this doesn't exist. That can't be true, right? Thanks for any ideas about how to make this work!Barb

[shyam_9]

Hi @Barb Krienke, Please use %sql in Databricks notebook cell.

%sql
SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

[Barb]

Hi @Shyamprasad Miryala​ 

I added %sql to the notebook cell and it did not work.

Were you able to get it to work in Databricks?

[jconno]

%sql

--The closest thing I found was instr()

SELECT instr(Attribute, 'MatchString')

FROM TableA tba

[Barb]

Thanks for trying @jconno​ 

Unfortunately I need to be able to specify the position where the search for the character needs to start. So just finding the first occurrence of the character is not going to help.

[RAJUMASARAPU]

Hi @bkrienke,

It may usefull.

%sql

SELECT position('bar', 'foobarbar', 5);

-- 7

[Traveller]

The best option I found to replace CHARINDEX was LOCATE, examples from the Spark documentation below

> SELECT locate('bar', 'foobarbar', 5);

7

> SELECT POSITION('bar' IN 'foobarbar');

4