| <div class="codehilite"><pre><span></span><code><span class="c1">-- ascii</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">ascii</span><span class="p">(</span><span class="s1">'222'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------+</span> |
| <span class="o">|</span><span class="n">ascii</span><span class="p">(</span><span class="mi">222</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">50</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">ascii</span><span class="p">(</span><span class="mi">2</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| <span class="o">|</span><span class="n">ascii</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">50</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| |
| <span class="c1">-- base64</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">base64</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------+</span> |
| <span class="o">|</span><span class="n">base64</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">U3BhcmsgU1FM</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------+</span> |
| |
| <span class="c1">-- bit_length</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">bit_length</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="k">bit_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">72</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| |
| <span class="c1">-- btrim</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">btrim</span><span class="p">(</span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">btrim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">btrim</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">' SparkSQL '</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">));</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| <span class="o">|</span><span class="n">btrim</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">btrim</span><span class="p">(</span><span class="s1">'SSparkSQLS'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="n">btrim</span><span class="p">(</span><span class="n">SSparkSQLS</span><span class="p">,</span><span class="w"> </span><span class="n">SL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">parkSQ</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">btrim</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'SSparkSQLS'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'SL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">));</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">btrim</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">SSparkSQLS</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="n">SL</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">parkSQ</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| |
| <span class="c1">-- char</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="nb">char</span><span class="p">(</span><span class="mi">65</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| <span class="o">|</span><span class="nb">char</span><span class="p">(</span><span class="mi">65</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------+</span> |
| |
| <span class="c1">-- char_length</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">char_length</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="k">char_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHAR_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="k">char_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHARACTER_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="k">character_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="c1">-- character_length</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">character_length</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="k">character_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHAR_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="k">char_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHARACTER_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="k">character_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="c1">-- chr</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">chr</span><span class="p">(</span><span class="mi">65</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------+</span> |
| <span class="o">|</span><span class="n">chr</span><span class="p">(</span><span class="mi">65</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------+</span> |
| |
| <span class="c1">-- concat_ws</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">concat_ws</span><span class="p">(</span><span class="s1">' '</span><span class="p">,</span><span class="w"> </span><span class="s1">'Spark'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| <span class="o">|</span><span class="n">concat_ws</span><span class="p">(</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">Spark</span><span class="p">,</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">concat_ws</span><span class="p">(</span><span class="s1">'s'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------+</span> |
| <span class="o">|</span><span class="n">concat_ws</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+</span> |
| |
| <span class="c1">-- contains</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">contains</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Spark'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="k">contains</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">Spark</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">contains</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SPARK'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="k">contains</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">SPARK</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">false</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">contains</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="k">null</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="k">contains</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">contains</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="s1">'537061726b'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| <span class="o">|</span><span class="k">contains</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'537061726B'</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| |
| <span class="c1">-- decode</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">decode</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">),</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="n">decode</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">abc</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">decode</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'Southlake'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'San Francisco'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'New Jersey'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="s1">'Seattle'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Non domestic'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">decode</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">Southlake</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="n">San</span><span class="w"> </span><span class="n">Francisco</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="k">New</span><span class="w"> </span><span class="n">Jersey</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="n">Seattle</span><span class="p">,</span><span class="w"> </span><span class="n">Non</span><span class="w"> </span><span class="n">domestic</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">San</span><span class="w"> </span><span class="n">Francisco</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">decode</span><span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'Southlake'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'San Francisco'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'New Jersey'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="s1">'Seattle'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Non domestic'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">decode</span><span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">Southlake</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="n">San</span><span class="w"> </span><span class="n">Francisco</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="k">New</span><span class="w"> </span><span class="n">Jersey</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="n">Seattle</span><span class="p">,</span><span class="w"> </span><span class="n">Non</span><span class="w"> </span><span class="n">domestic</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Non</span><span class="w"> </span><span class="n">domestic</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">decode</span><span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'Southlake'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'San Francisco'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="s1">'New Jersey'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="s1">'Seattle'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">decode</span><span class="p">(</span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">Southlake</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="n">San</span><span class="w"> </span><span class="n">Francisco</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="k">New</span><span class="w"> </span><span class="n">Jersey</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="n">Seattle</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">decode</span><span class="p">(</span><span class="k">null</span><span class="p">,</span><span class="w"> </span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="s1">'Spark'</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="s1">'SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="s1">'rocks'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| <span class="o">|</span><span class="n">decode</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="n">Spark</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span><span class="w"> </span><span class="n">rocks</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| |
| <span class="c1">-- elt</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">elt</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'scala'</span><span class="p">,</span><span class="w"> </span><span class="s1">'java'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="n">elt</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">scala</span><span class="p">,</span><span class="w"> </span><span class="n">java</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">scala</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| |
| <span class="c1">-- encode</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="n">encode</span><span class="p">(</span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">61</span><span class="w"> </span><span class="mi">62</span><span class="w"> </span><span class="mi">63</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| |
| <span class="c1">-- endswith</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">endswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| <span class="o">|</span><span class="n">endswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">endswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Spark'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="n">endswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">Spark</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">false</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">endswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="k">null</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">endswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">endswith</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="s1">'537061726b'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| <span class="o">|</span><span class="n">endswith</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'537061726B'</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">false</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">endswith</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="s1">'53514c'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------------+</span> |
| <span class="o">|</span><span class="n">endswith</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'53514C'</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------+</span> |
| |
| <span class="c1">-- find_in_set</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">find_in_set</span><span class="p">(</span><span class="s1">'ab'</span><span class="p">,</span><span class="s1">'abc,b,ab,c,def'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="n">find_in_set</span><span class="p">(</span><span class="n">ab</span><span class="p">,</span><span class="w"> </span><span class="n">abc</span><span class="p">,</span><span class="n">b</span><span class="p">,</span><span class="n">ab</span><span class="p">,</span><span class="k">c</span><span class="p">,</span><span class="n">def</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| |
| <span class="c1">-- format_number</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">format_number</span><span class="p">(</span><span class="mi">12332</span><span class="p">.</span><span class="mi">123456</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------+</span> |
| <span class="o">|</span><span class="n">format_number</span><span class="p">(</span><span class="mi">12332</span><span class="p">.</span><span class="mi">123456</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">12</span><span class="p">,</span><span class="mi">332</span><span class="p">.</span><span class="mi">1235</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">format_number</span><span class="p">(</span><span class="mi">12332</span><span class="p">.</span><span class="mi">123456</span><span class="p">,</span><span class="w"> </span><span class="s1">'##################.###'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">format_number</span><span class="p">(</span><span class="mi">12332</span><span class="p">.</span><span class="mi">123456</span><span class="p">,</span><span class="w"> </span><span class="o">##################</span><span class="p">.</span><span class="o">###</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">12332</span><span class="p">.</span><span class="mi">123</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------------------+</span> |
| |
| <span class="c1">-- format_string</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">format_string</span><span class="p">(</span><span class="ss">"Hello World %d %s"</span><span class="p">,</span><span class="w"> </span><span class="mi">100</span><span class="p">,</span><span class="w"> </span><span class="ss">"days"</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| <span class="o">|</span><span class="n">format_string</span><span class="p">(</span><span class="n">Hello</span><span class="w"> </span><span class="n">World</span><span class="w"> </span><span class="o">%</span><span class="n">d</span><span class="w"> </span><span class="o">%</span><span class="n">s</span><span class="p">,</span><span class="w"> </span><span class="mi">100</span><span class="p">,</span><span class="w"> </span><span class="n">days</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Hello</span><span class="w"> </span><span class="n">World</span><span class="w"> </span><span class="mi">100</span><span class="w"> </span><span class="n">days</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------+</span> |
| |
| <span class="c1">-- initcap</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">initcap</span><span class="p">(</span><span class="s1">'sPark sql'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="n">initcap</span><span class="p">(</span><span class="n">sPark</span><span class="w"> </span><span class="k">sql</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spark</span><span class="w"> </span><span class="k">Sql</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| |
| <span class="c1">-- instr</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">instr</span><span class="p">(</span><span class="s1">'SparkSQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| <span class="o">|</span><span class="n">instr</span><span class="p">(</span><span class="n">SparkSQL</span><span class="p">,</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">6</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| |
| <span class="c1">-- lcase</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">lcase</span><span class="p">(</span><span class="s1">'SparkSql'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">lcase</span><span class="p">(</span><span class="n">SparkSql</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">sparksql</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="c1">-- left</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">left</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="k">left</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spa</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| |
| <span class="c1">-- length</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">length</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="k">length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHAR_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="k">char_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">CHARACTER_LENGTH</span><span class="p">(</span><span class="s1">'Spark SQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="k">character_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">10</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="c1">-- levenshtein</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">levenshtein</span><span class="p">(</span><span class="s1">'kitten'</span><span class="p">,</span><span class="w"> </span><span class="s1">'sitting'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="n">levenshtein</span><span class="p">(</span><span class="n">kitten</span><span class="p">,</span><span class="w"> </span><span class="n">sitting</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="c1">-- locate</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">locate</span><span class="p">(</span><span class="s1">'bar'</span><span class="p">,</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">locate</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">4</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">locate</span><span class="p">(</span><span class="s1">'bar'</span><span class="p">,</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">locate</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">7</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">POSITION</span><span class="p">(</span><span class="s1">'bar'</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">locate</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">4</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="c1">-- lower</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">lower</span><span class="p">(</span><span class="s1">'SparkSql'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="k">lower</span><span class="p">(</span><span class="n">SparkSql</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">sparksql</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="c1">-- lpad</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">lpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="s1">'??'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">lpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="o">??</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="o">???</span><span class="n">hi</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">lpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'??'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">lpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="o">??</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">h</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">lpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="n">lpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">hi</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">hex</span><span class="p">(</span><span class="n">lpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="s1">'aabb'</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">));</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">hex</span><span class="p">(</span><span class="n">lpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="n">aabb</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'00'</span><span class="p">))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">000000</span><span class="n">AABB</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">hex</span><span class="p">(</span><span class="n">lpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="s1">'aabb'</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">unhex</span><span class="p">(</span><span class="s1">'1122'</span><span class="p">)));</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="n">hex</span><span class="p">(</span><span class="n">lpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="n">aabb</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">unhex</span><span class="p">(</span><span class="mi">1122</span><span class="p">)))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">112211</span><span class="n">AABB</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| |
| <span class="c1">-- ltrim</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">ltrim</span><span class="p">(</span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">ltrim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="c1">-- octet_length</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">octet_length</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="k">octet_length</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">9</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="c1">-- overlay</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="s1">'_'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">6</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">_</span><span class="p">,</span><span class="w"> </span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spark_SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="s1">'CORE'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">7</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">CORE</span><span class="p">,</span><span class="w"> </span><span class="mi">7</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spark</span><span class="w"> </span><span class="n">CORE</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="s1">'ANSI '</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">7</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">0</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">ANSI</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="mi">7</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Spark</span><span class="w"> </span><span class="n">ANSI</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="s1">'tructured'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">4</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">tructured</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Structured</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'_'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">6</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="n">_</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="mi">6</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">53</span><span class="w"> </span><span class="mi">70</span><span class="w"> </span><span class="mi">61</span><span class="w"> </span><span class="mi">72</span><span class="w"> </span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">5</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'CORE'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">7</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="n">CORE</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="mi">7</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">53</span><span class="w"> </span><span class="mi">70</span><span class="w"> </span><span class="mi">61</span><span class="w"> </span><span class="mi">72</span><span class="w"> </span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">2</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'ANSI '</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">7</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">0</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="n">ANSI</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="mi">7</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">53</span><span class="w"> </span><span class="mi">70</span><span class="w"> </span><span class="mi">61</span><span class="w"> </span><span class="mi">72</span><span class="w"> </span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">2</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">PLACING</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="s1">'tructured'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">4</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="k">overlay</span><span class="p">(</span><span class="n">encode</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="n">encode</span><span class="p">(</span><span class="n">tructured</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">),</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">4</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">53</span><span class="w"> </span><span class="mi">74</span><span class="w"> </span><span class="mi">72</span><span class="w"> </span><span class="mi">75</span><span class="w"> </span><span class="mi">63</span><span class="w"> </span><span class="mi">7</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------------------+</span> |
| |
| <span class="c1">-- parse_url</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">parse_url</span><span class="p">(</span><span class="s1">'http://spark.apache.org/path?query=1'</span><span class="p">,</span><span class="w"> </span><span class="s1">'HOST'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">parse_url</span><span class="p">(</span><span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">spark</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">path</span><span class="o">?</span><span class="n">query</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">HOST</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">spark</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">parse_url</span><span class="p">(</span><span class="s1">'http://spark.apache.org/path?query=1'</span><span class="p">,</span><span class="w"> </span><span class="s1">'QUERY'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">parse_url</span><span class="p">(</span><span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">spark</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">path</span><span class="o">?</span><span class="n">query</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">QUERY</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">query</span><span class="o">=</span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">parse_url</span><span class="p">(</span><span class="s1">'http://spark.apache.org/path?query=1'</span><span class="p">,</span><span class="w"> </span><span class="s1">'QUERY'</span><span class="p">,</span><span class="w"> </span><span class="s1">'query'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">parse_url</span><span class="p">(</span><span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">spark</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">path</span><span class="o">?</span><span class="n">query</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">QUERY</span><span class="p">,</span><span class="w"> </span><span class="n">query</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------+</span> |
| |
| <span class="c1">-- position</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">position</span><span class="p">(</span><span class="s1">'bar'</span><span class="p">,</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="k">position</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">4</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">position</span><span class="p">(</span><span class="s1">'bar'</span><span class="p">,</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="k">position</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">7</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">POSITION</span><span class="p">(</span><span class="s1">'bar'</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="s1">'foobarbar'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">locate</span><span class="p">(</span><span class="n">bar</span><span class="p">,</span><span class="w"> </span><span class="n">foobarbar</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">4</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="c1">-- printf</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">printf</span><span class="p">(</span><span class="ss">"Hello World %d %s"</span><span class="p">,</span><span class="w"> </span><span class="mi">100</span><span class="p">,</span><span class="w"> </span><span class="ss">"days"</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="n">printf</span><span class="p">(</span><span class="n">Hello</span><span class="w"> </span><span class="n">World</span><span class="w"> </span><span class="o">%</span><span class="n">d</span><span class="w"> </span><span class="o">%</span><span class="n">s</span><span class="p">,</span><span class="w"> </span><span class="mi">100</span><span class="p">,</span><span class="w"> </span><span class="n">days</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">Hello</span><span class="w"> </span><span class="n">World</span><span class="w"> </span><span class="mi">100</span><span class="w"> </span><span class="n">days</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| |
| <span class="c1">-- regexp_extract</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_extract</span><span class="p">(</span><span class="s1">'100-200'</span><span class="p">,</span><span class="w"> </span><span class="s1">'(\\d+)-(\\d+)'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_extract</span><span class="p">(</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="p">,</span><span class="w"> </span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">)</span><span class="o">-</span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">),</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">100</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------------+</span> |
| |
| <span class="c1">-- regexp_extract_all</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_extract_all</span><span class="p">(</span><span class="s1">'100-200, 300-400'</span><span class="p">,</span><span class="w"> </span><span class="s1">'(\\d+)-(\\d+)'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_extract_all</span><span class="p">(</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="p">,</span><span class="w"> </span><span class="mi">300</span><span class="o">-</span><span class="mi">400</span><span class="p">,</span><span class="w"> </span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">)</span><span class="o">-</span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">),</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">100</span><span class="p">,</span><span class="w"> </span><span class="mi">300</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------------------------------+</span> |
| |
| <span class="c1">-- regexp_replace</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_replace</span><span class="p">(</span><span class="s1">'100-200'</span><span class="p">,</span><span class="w"> </span><span class="s1">'(\\d+)'</span><span class="p">,</span><span class="w"> </span><span class="s1">'num'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_replace</span><span class="p">(</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="p">,</span><span class="w"> </span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">),</span><span class="w"> </span><span class="n">num</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">num</span><span class="o">-</span><span class="n">num</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| |
| <span class="c1">-- repeat</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">repeat</span><span class="p">(</span><span class="s1">'123'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="n">repeat</span><span class="p">(</span><span class="mi">123</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">123123</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| |
| <span class="c1">-- replace</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">replace</span><span class="p">(</span><span class="s1">'ABCabc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'DEF'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="k">replace</span><span class="p">(</span><span class="n">ABCabc</span><span class="p">,</span><span class="w"> </span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">DEF</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">ABCDEF</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="c1">-- right</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">right</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="k">right</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| |
| <span class="c1">-- rpad</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">rpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="s1">'??'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">rpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="o">??</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">hi</span><span class="o">???|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">rpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'??'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">rpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="o">??</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">h</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">rpad</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="n">rpad</span><span class="p">(</span><span class="n">hi</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">hi</span><span class="w"> </span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">hex</span><span class="p">(</span><span class="n">rpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="s1">'aabb'</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">));</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">hex</span><span class="p">(</span><span class="n">rpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="n">aabb</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'00'</span><span class="p">))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">AABB000000</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">hex</span><span class="p">(</span><span class="n">rpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="s1">'aabb'</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">unhex</span><span class="p">(</span><span class="s1">'1122'</span><span class="p">)));</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="n">hex</span><span class="p">(</span><span class="n">rpad</span><span class="p">(</span><span class="n">unhex</span><span class="p">(</span><span class="n">aabb</span><span class="p">),</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="n">unhex</span><span class="p">(</span><span class="mi">1122</span><span class="p">)))</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">AABB112211</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| |
| <span class="c1">-- rtrim</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">rtrim</span><span class="p">(</span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">rtrim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="c1">-- sentences</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">sentences</span><span class="p">(</span><span class="s1">'Hi there! Good morning.'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="n">sentences</span><span class="p">(</span><span class="n">Hi</span><span class="w"> </span><span class="n">there</span><span class="o">!</span><span class="w"> </span><span class="n">Good</span><span class="w"> </span><span class="n">morning</span><span class="p">.,</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[[</span><span class="n">Hi</span><span class="p">,</span><span class="w"> </span><span class="n">there</span><span class="p">],</span><span class="w"> </span><span class="p">[</span><span class="k">Go</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| |
| <span class="c1">-- soundex</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">soundex</span><span class="p">(</span><span class="s1">'Miller'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">soundex</span><span class="p">(</span><span class="n">Miller</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">M460</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="c1">-- space</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">concat</span><span class="p">(</span><span class="k">space</span><span class="p">(</span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="s1">'1'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="n">concat</span><span class="p">(</span><span class="k">space</span><span class="p">(</span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| |
| <span class="c1">-- split</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">split</span><span class="p">(</span><span class="s1">'oneAtwoBthreeC'</span><span class="p">,</span><span class="w"> </span><span class="s1">'[ABC]'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">split</span><span class="p">(</span><span class="n">oneAtwoBthreeC</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="n">ABC</span><span class="p">],</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="n">one</span><span class="p">,</span><span class="w"> </span><span class="n">two</span><span class="p">,</span><span class="w"> </span><span class="n">three</span><span class="p">,</span><span class="w"> </span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">split</span><span class="p">(</span><span class="s1">'oneAtwoBthreeC'</span><span class="p">,</span><span class="w"> </span><span class="s1">'[ABC]'</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">split</span><span class="p">(</span><span class="n">oneAtwoBthreeC</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="n">ABC</span><span class="p">],</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="n">one</span><span class="p">,</span><span class="w"> </span><span class="n">two</span><span class="p">,</span><span class="w"> </span><span class="n">three</span><span class="p">,</span><span class="w"> </span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">split</span><span class="p">(</span><span class="s1">'oneAtwoBthreeC'</span><span class="p">,</span><span class="w"> </span><span class="s1">'[ABC]'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="n">split</span><span class="p">(</span><span class="n">oneAtwoBthreeC</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="n">ABC</span><span class="p">],</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="n">one</span><span class="p">,</span><span class="w"> </span><span class="n">twoBthreeC</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| |
| <span class="c1">-- split_part</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">split_part</span><span class="p">(</span><span class="s1">'11.12.13'</span><span class="p">,</span><span class="w"> </span><span class="s1">'.'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="n">split_part</span><span class="p">(</span><span class="mi">11</span><span class="p">.</span><span class="mi">12</span><span class="p">.</span><span class="mi">13</span><span class="p">,</span><span class="w"> </span><span class="p">.,</span><span class="w"> </span><span class="mi">3</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">13</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="c1">-- startswith</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">startswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Spark'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="n">startswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="n">Spark</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">startswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="s1">'SQL'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="n">startswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="k">SQL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">false</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">startswith</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="k">null</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="n">startswith</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">startswith</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="s1">'537061726b'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">startswith</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'537061726B'</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">startswith</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="s1">'53514c'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------+</span> |
| <span class="o">|</span><span class="n">startswith</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="s1">'53514C'</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">false</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------+</span> |
| |
| <span class="c1">-- substr</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">substr</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="n">substr</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">substr</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substr</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">5</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="c1">-- substring</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">5</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="o">-</span><span class="mi">3</span><span class="p">,</span><span class="w"> </span><span class="mi">2147483647</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">SQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">substring</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="mi">5</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="k">substring</span><span class="p">(</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="p">,</span><span class="w"> </span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">k</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| |
| <span class="c1">-- substring_index</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">substring_index</span><span class="p">(</span><span class="s1">'www.apache.org'</span><span class="p">,</span><span class="w"> </span><span class="s1">'.'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| <span class="o">|</span><span class="n">substring_index</span><span class="p">(</span><span class="n">www</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="p">,</span><span class="w"> </span><span class="p">.,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">www</span><span class="p">.</span><span class="n">apache</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------------+</span> |
| |
| <span class="c1">-- to_binary</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_binary</span><span class="p">(</span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="n">to_binary</span><span class="p">(</span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">61</span><span class="w"> </span><span class="mi">62</span><span class="w"> </span><span class="mi">63</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| |
| <span class="c1">-- to_number</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_number</span><span class="p">(</span><span class="s1">'454'</span><span class="p">,</span><span class="w"> </span><span class="s1">'999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="n">to_number</span><span class="p">(</span><span class="mi">454</span><span class="p">,</span><span class="w"> </span><span class="mi">999</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">454</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_number</span><span class="p">(</span><span class="s1">'454.00'</span><span class="p">,</span><span class="w"> </span><span class="s1">'000.00'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">to_number</span><span class="p">(</span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="p">,</span><span class="w"> </span><span class="mi">000</span><span class="p">.</span><span class="mi">00</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_number</span><span class="p">(</span><span class="s1">'12,454'</span><span class="p">,</span><span class="w"> </span><span class="s1">'99,999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">to_number</span><span class="p">(</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">12454</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_number</span><span class="p">(</span><span class="s1">'$78.12'</span><span class="p">,</span><span class="w"> </span><span class="s1">'$99.99'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">to_number</span><span class="p">(</span><span class="err">$</span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="p">,</span><span class="w"> </span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_number</span><span class="p">(</span><span class="s1">'12,454.8-'</span><span class="p">,</span><span class="w"> </span><span class="s1">'99,999.9S'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="n">to_number</span><span class="p">(</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="p">.</span><span class="mi">8</span><span class="o">-</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="p">.</span><span class="mi">9</span><span class="n">S</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| |
| <span class="c1">-- translate</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">translate</span><span class="p">(</span><span class="s1">'AaBbCc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'123'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="k">translate</span><span class="p">(</span><span class="n">AaBbCc</span><span class="p">,</span><span class="w"> </span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="mi">123</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1B2C3</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------+</span> |
| |
| <span class="c1">-- trim</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="k">trim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">BOTH</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="k">trim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">LEADING</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">ltrim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">TRAILING</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">' SparkSQL '</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">rtrim</span><span class="p">(</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SparkSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="s1">'SL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">'SSparkSQLS'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="k">TRIM</span><span class="p">(</span><span class="k">BOTH</span><span class="w"> </span><span class="n">SL</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">SSparkSQLS</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">parkSQ</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">BOTH</span><span class="w"> </span><span class="s1">'SL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">'SSparkSQLS'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="k">TRIM</span><span class="p">(</span><span class="k">BOTH</span><span class="w"> </span><span class="n">SL</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">SSparkSQLS</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">parkSQ</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">LEADING</span><span class="w"> </span><span class="s1">'SL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">'SSparkSQLS'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="k">TRIM</span><span class="p">(</span><span class="k">LEADING</span><span class="w"> </span><span class="n">SL</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">SSparkSQLS</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">parkSQLS</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">trim</span><span class="p">(</span><span class="k">TRAILING</span><span class="w"> </span><span class="s1">'SL'</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">'SSparkSQLS'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="k">TRIM</span><span class="p">(</span><span class="k">TRAILING</span><span class="w"> </span><span class="n">SL</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">SSparkSQLS</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SSparkSQ</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| |
| <span class="c1">-- try_to_binary</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_binary</span><span class="p">(</span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'utf-8'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_binary</span><span class="p">(</span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">61</span><span class="w"> </span><span class="mi">62</span><span class="w"> </span><span class="mi">63</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">select</span><span class="w"> </span><span class="n">try_to_binary</span><span class="p">(</span><span class="s1">'a!'</span><span class="p">,</span><span class="w"> </span><span class="s1">'base64'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_binary</span><span class="p">(</span><span class="n">a</span><span class="o">!</span><span class="p">,</span><span class="w"> </span><span class="n">base64</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| |
| <span class="k">select</span><span class="w"> </span><span class="n">try_to_binary</span><span class="p">(</span><span class="s1">'abc'</span><span class="p">,</span><span class="w"> </span><span class="s1">'invalidFormat'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_binary</span><span class="p">(</span><span class="n">abc</span><span class="p">,</span><span class="w"> </span><span class="n">invalidFormat</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| |
| <span class="c1">-- try_to_number</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_number</span><span class="p">(</span><span class="s1">'454'</span><span class="p">,</span><span class="w"> </span><span class="s1">'999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">try_to_number</span><span class="p">(</span><span class="mi">454</span><span class="p">,</span><span class="w"> </span><span class="mi">999</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">454</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_number</span><span class="p">(</span><span class="s1">'454.00'</span><span class="p">,</span><span class="w"> </span><span class="s1">'000.00'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_number</span><span class="p">(</span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="p">,</span><span class="w"> </span><span class="mi">000</span><span class="p">.</span><span class="mi">00</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_number</span><span class="p">(</span><span class="s1">'12,454'</span><span class="p">,</span><span class="w"> </span><span class="s1">'99,999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_number</span><span class="p">(</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">12454</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_number</span><span class="p">(</span><span class="s1">'$78.12'</span><span class="p">,</span><span class="w"> </span><span class="s1">'$99.99'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_number</span><span class="p">(</span><span class="err">$</span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="p">,</span><span class="w"> </span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">try_to_number</span><span class="p">(</span><span class="s1">'12,454.8-'</span><span class="p">,</span><span class="w"> </span><span class="s1">'99,999.9S'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="n">try_to_number</span><span class="p">(</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="p">.</span><span class="mi">8</span><span class="o">-</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="p">.</span><span class="mi">9</span><span class="n">S</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| |
| <span class="c1">-- ucase</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">ucase</span><span class="p">(</span><span class="s1">'SparkSql'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="n">ucase</span><span class="p">(</span><span class="n">SparkSql</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SPARKSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| |
| <span class="c1">-- unbase64</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">unbase64</span><span class="p">(</span><span class="s1">'U3BhcmsgU1FM'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">unbase64</span><span class="p">(</span><span class="n">U3BhcmsgU1FM</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">53</span><span class="w"> </span><span class="mi">70</span><span class="w"> </span><span class="mi">61</span><span class="w"> </span><span class="mi">72</span><span class="w"> </span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">2</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="c1">-- upper</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">upper</span><span class="p">(</span><span class="s1">'SparkSql'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="k">upper</span><span class="p">(</span><span class="n">SparkSql</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">SPARKSQL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------+</span> |
| </code></pre></div> |
