I was querying values from two tables, which were joined, to find out the reason for mismatch in pyspark.

Joining columns from the tables seems to have extra space in one column.

I tried trim functions(multiple combinations) to remove the white space but it was futile.

There was one suggestion to try with hex() function. Below is the code and output for the same. Can someone tell me how to interpret hex?

compare_query="""
(
select coalesce(upper(trim(b.cleaned)),upper(trim(a.tz_brandname))) as tz_brandname_mapped, 
b.source,length(b.source),hex(b.source),
a.productbrand,length(a.productbrand),hex(a.productbrand)
from ticket a
left join brand_mapping b
on trim(a.productbrand)=trim(b.source)
where ticketid='xxx'
and ticketlineid='xxx'
and storeid='xxx'
and dateclosed='xxx'
)
"""

compare_df=spark.sql(compare_query)

compare_df.show(truncate=False)

Output

FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…
+-------------------+------+--------------+------------+------------+--------------------+-----------------+
|tz_brandname_mapped|source|length(source)|hex(source) |productbrand|length(productbrand)|hex(productbrand)|
+-------------------+------+--------------+------------+------------+--------------------+-----------------+
|SOURCE CANNABIS    |SOURCE|6             |534F55524345| SOURCE     |7                   |20534F55524345   |
+-------------------+------+--------------+------------+------------+--------------------+-----------------+