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

[joeyslaptop]

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

[JAHNAVI · Accepted solution]

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