cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

SQL charindex function?

Barb
New Contributor III

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

6 REPLIES 6

shyam_9
Databricks Employee
Databricks Employee

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

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

Barb
New Contributor III

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
New Contributor II

%sql

--The closest thing I found was instr()

SELECT instr(Attribute, 'MatchString')

FROM TableA tba

Barb
New Contributor III

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
New Contributor II

Hi @bkrienke,

It may usefull.

%sql

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

-- 7

Traveller
New Contributor II

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group