In a Databricks SQL endpoint, didn't find SQL server PATINDEX equal function in databricks

Question

SQL Server

Ex:- SELECT PATINDEX('%[0-9]%', 'Apple5Ball6') Test

Result:- 6

How can we get same result in Databricks SQL end point?

Answer 1

In Databricks SQL, there isn't a direct equivalent to the SQL Server's PATINDEX function. However, you can achieve a similar result using regular expressions with the REGEXP_INSTR function.

To get the same result as your SQL Server example, you can use the following query in Databricks SQL endpoint:

SELECT REGEXP_INSTR('Apple5Ball6', '[0-9]') AS Test

The REGEXP_INSTR function takes two arguments: the first argument is the input string, and the second argument is the regular expression pattern. In this case, the pattern '[0-9]' matches any single digit in the input string.

The function will return the position of the first occurrence of the pattern in the input string, so the result will be 6, just like in your SQL Server example.