SQL LIKE in Spark SQL
<p>I'm trying to implement a join in Spark SQL using a LIKE condition.</p>

<p>The row I am performing the join on looks like this and is called 'revision':</p>

<p>Table A:</p>

<pre><code>8NXDPVAE
</code></pre>

<p>Table B:</p>

<pre><code>[4,8]NXD_V%
</code></pre>

<p>Performing the join on SQL server (<code>A.revision LIKE B.revision</code>) works just fine, but when doing the same in Spark SQL, the join returns no rows (if using inner join) or null values for Table B (if using outer join).</p>

<p>This is the query I am running:</p>

<pre><code>val joined = spark.sql("SELECT A.revision, B.revision FROM RAWDATA A LEFT JOIN TPTYPE B ON A.revision LIKE B.revision")
</code></pre>

<p>The plan looks like this:</p>

<pre><code>== Physical Plan ==
BroadcastNestedLoopJoin BuildLeft, LeftOuter, revision#15 LIKE revision#282, false
:- BroadcastExchange IdentityBroadcastMode
:  +- *Project [revision#15]
:     +- *Scan JDBCRelation(RAWDATA) [revision#15] PushedFilters: [EqualTo(bulk_id,2016092419270100198)], ReadSchema: struct&lt;revision&gt;
+- *Scan JDBCRelation(TPTYPE) [revision#282] ReadSchema: struct&lt;revision&gt;
</code></pre>

<p>Is it possible to perform a LIKE join like this or am I way off?</p>