topic Re: SQL charindex function? in Data Engineering

SQL charindex function?

Barb — Mon, 07 Oct 2019 16:05:30 GMT

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

Re: SQL charindex function?

shyam_9 — Mon, 07 Oct 2019 16:28:14 GMT

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

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

Re: SQL charindex function?

jconno — Tue, 15 Oct 2019 03:10:59 GMT

%sql

--The closest thing I found was instr()

SELECT instr(Attribute, 'MatchString')

FROM TableA tba

Re: SQL charindex function?

Barb — Mon, 30 Dec 2019 05:04:21 GMT

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?

Re: SQL charindex function?

Barb — Mon, 30 Dec 2019 05:06:03 GMT

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.

Re: SQL charindex function?

RAJUMASARAPU — Thu, 05 Mar 2020 03:48:37 GMT

Hi @bkrienke,

It may usefull.

%sql

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

-- 7

Re: SQL charindex function?

Traveller — Wed, 14 Oct 2020 05:14:32 GMT

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

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

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