How do I use DataBricks SQL query to convert a field value % back into a wildcard?

joeyslaptop — Tue, 19 Nov 2024 19:54:02 GMT

Hi. If I've posted to the wrong area, please let me know.

I am using SQL to join two tables. One table has the wildcard '%' stored as text/string/varchar. I need to join the value of TableA.column1 to TableB.column1 based on the wildcard in the string being treated as a wildcard.

For example:

TableA.Column1	TableA.Column2
Cat	animal
Dragon	animal
Elephant	animal

TableB.Column1	TableB.Column2
Ca%	Do not eat
D%	Do not eat
Ele%	Do not eat
Ch%	Can eat

The result is that I want to left join TableB onto TableA like this:
SELECT
A.*, B.Column2
FROM TableA A
LEFT JOIN TableB B
ON A.Column1 like B.Column1

Re: How do I use DataBricks SQL query to convert a field value % back into a wildcard?

JAHNAVI — Wed, 20 Nov 2024 15:22:27 GMT

Hi,

Could you please try the query below and let me know if it meets your requirements?

SELECT *

FROM TableA A

LEFT JOIN TableB B

ON A.Column1 LIKE REPLACE(B.Column1, '%', '%%')

Replace helps us in treating the %' stored in TableB.Column1 as a wildcard

topic How do I use DataBricks SQL query to convert a field value % back into a wildcard? in Data Engineering

How do I use DataBricks SQL query to convert a field value % back into a wildcard?

Re: How do I use DataBricks SQL query to convert a field value % back into a wildcard?