Extraneous input '\' expecting {')', ','}. Query working in sql notebook cell but not in databricks connect

Question

When executing the following query

SELECT * FROM companies JOIN employments on companies.company_normalized LIKE CONCAT('%',
    replace(employments.displayname_normalized, '\\', ''), '%') or employments.displayname_normalized LIKE CONCAT(
    '%', replace(companies.company_normalized,'\\', ''), '%')

it works fine on databricks sql notebook cell.

However, when attempting to run this same query using spark sql executor, ie spark.sql(query), this errors out saying: Extraneous input '' expecting {')', ','} within the replace clause.

Is there a fix for this?

Try replacing \\ by \\\\ when using spark.sql()

blackbishop
– blackbishop

2022-02-10 09:51:13 +00:00
Commented Feb 10, 2022 at 9:51 — blackbishop
– blackbishop, Commented Feb 10, 2022 at 9:51

partha_devArch · Accepted Answer · 2022-02-10 13:02:41Z

1

You need either a single backslash, if you are using regex:

spark.sql("SELECT * FROM companies JOIN employments on companies.company_normalized LIKE CONCAT('%',
replace(employments.displayname_normalized, '\', ''), '%') or employments.displayname_normalized LIKE CONCAT(
'%', replace(companies.company_normalized,'\', ''), '%')")

or you need to escape slashes, then you need to double escape using "\\\\":

spark.sql("SELECT * FROM companies JOIN employments on companies.company_normalized LIKE CONCAT('%',
replace(employments.displayname_normalized, '\\\\', ''), '%') or employments.displayname_normalized LIKE CONCAT(
'%', replace(companies.company_normalized,'\\\\', ''), '%')")

answered Feb 10, 2022 at 13:02

partha_devArch

4542 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coderboi Over a year ago

does this still apply if I want to replace the \ character?

Collectives™ on Stack Overflow

Extraneous input '\' expecting {')', ','}. Query working in sql notebook cell but not in databricks connect

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related