| <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="k">SELECT</span><span class="w"> </span><span class="n">base64</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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="k">SELECT</span><span class="w"> </span><span class="k">bit_length</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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="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">character_length</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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="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="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">'foo'</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">'bar'</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="o">/</span><span class="p">,</span><span class="w"> </span><span class="n">foo</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">bar</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">foo</span><span class="o">/</span><span class="n">bar</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="k">null</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="k">NULL</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="k">NULL</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="k">SELECT</span><span class="w"> </span><span class="n">elt</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">'a'</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">elt</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="n">a</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">-- 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="k">SELECT</span><span class="w"> </span><span class="k">left</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="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">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="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="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="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---------------------------------+</span> |
| |
| <span class="c1">-- len</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">len</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">len</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="n">len</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="n">len</span><span class="p">(</span><span class="n">X</span><span class="s1">'537061726B2053514C'</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="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">-- 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">length</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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="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="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="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">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="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="o">-</span><span class="mi">1</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">-- luhn_check</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">luhn_check</span><span class="p">(</span><span class="s1">'8112189876'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">luhn_check</span><span class="p">(</span><span class="mi">8112189876</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">luhn_check</span><span class="p">(</span><span class="s1">'79927398713'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">luhn_check</span><span class="p">(</span><span class="mi">79927398713</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">luhn_check</span><span class="p">(</span><span class="s1">'79927398714'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">luhn_check</span><span class="p">(</span><span class="mi">79927398714</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">-- mask</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">mask</span><span class="p">(</span><span class="s1">'abcd-EFGH-8765-4321'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">abcd</span><span class="o">-</span><span class="n">EFGH</span><span class="o">-</span><span class="mi">8765</span><span class="o">-</span><span class="mi">4321</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">xxxx</span><span class="o">-</span><span class="n">XXXX</span><span class="o">-</span><span class="n">nnnn</span><span class="o">-</span><span class="n">nnnn</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">mask</span><span class="p">(</span><span class="s1">'abcd-EFGH-8765-4321'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">abcd</span><span class="o">-</span><span class="n">EFGH</span><span class="o">-</span><span class="mi">8765</span><span class="o">-</span><span class="mi">4321</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">xxxx</span><span class="o">-</span><span class="n">QQQQ</span><span class="o">-</span><span class="n">nnnn</span><span class="o">-</span><span class="n">nnnn</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">,</span><span class="w"> </span><span class="s1">'q'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">q</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">QqQQnnn</span><span class="o">-@</span><span class="err">$</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">XxXXnnn</span><span class="o">-@</span><span class="err">$</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">QxQQnnn</span><span class="o">-@</span><span class="err">$</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">,</span><span class="w"> </span><span class="s1">'q'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">q</span><span class="p">,</span><span class="w"> </span><span class="n">n</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="n">QqQQnnn</span><span class="o">-@</span><span class="err">$</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">,</span><span class="w"> </span><span class="s1">'q'</span><span class="p">,</span><span class="w"> </span><span class="s1">'d'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">q</span><span class="p">,</span><span class="w"> </span><span class="n">d</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="n">QqQQddd</span><span class="o">-@</span><span class="err">$</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Q'</span><span class="p">,</span><span class="w"> </span><span class="s1">'q'</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">'o'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="n">Q</span><span class="p">,</span><span class="w"> </span><span class="n">q</span><span class="p">,</span><span class="w"> </span><span class="n">d</span><span class="p">,</span><span class="w"> </span><span class="n">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">QqQQdddoooo</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</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">'q'</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">'o'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">q</span><span class="p">,</span><span class="w"> </span><span class="n">d</span><span class="p">,</span><span class="w"> </span><span class="n">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">AqCDdddoooo</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</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">NULL</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">'o'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</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">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">d</span><span class="p">,</span><span class="w"> </span><span class="n">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">AbCDdddoooo</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</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">NULL</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">'o'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</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">NULL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">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">AbCD123oooo</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">mask</span><span class="p">(</span><span class="k">NULL</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">NULL</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">'o'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="k">NULL</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">NULL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">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="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">mask</span><span class="p">(</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">mask</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="n">X</span><span class="p">,</span><span class="w"> </span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">n</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">mask</span><span class="p">(</span><span class="s1">'AbCD123-@$#'</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">NULL</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">NULL</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------+</span> |
| <span class="o">|</span><span class="n">mask</span><span class="p">(</span><span class="n">AbCD123</span><span class="o">-@</span><span class="err">$</span><span class="o">#</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">NULL</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">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="n">AbCD123</span><span class="o">-@</span><span class="err">$</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="k">SELECT</span><span class="w"> </span><span class="k">octet_length</span><span class="p">(</span><span class="n">x</span><span class="s1">'537061726b2053514c'</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">X</span><span class="s1">'537061726B2053514C'</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">-- 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_count</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_count</span><span class="p">(</span><span class="s1">'Steven Jones and Stephen Smith are the best players'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Ste(v|ph)en'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_count</span><span class="p">(</span><span class="n">Steven</span><span class="w"> </span><span class="n">Jones</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="n">Stephen</span><span class="w"> </span><span class="n">Smith</span><span class="w"> </span><span class="k">are</span><span class="w"> </span><span class="n">the</span><span class="w"> </span><span class="n">best</span><span class="w"> </span><span class="n">players</span><span class="p">,</span><span class="w"> </span><span class="n">Ste</span><span class="p">(</span><span class="n">v</span><span class="o">|</span><span class="n">ph</span><span class="p">)</span><span class="n">en</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">2</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">regexp_count</span><span class="p">(</span><span class="s1">'abcdefghijklmnopqrstuvwxyz'</span><span class="p">,</span><span class="w"> </span><span class="s1">'[a-z]{3}'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_count</span><span class="p">(</span><span class="n">abcdefghijklmnopqrstuvwxyz</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="n">a</span><span class="o">-</span><span class="n">z</span><span class="p">]</span><span class="err">{</span><span class="mi">3</span><span class="err">}</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">8</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_instr</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_instr</span><span class="p">(</span><span class="s1">'user@spark.apache.org'</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">regexp_instr</span><span class="p">(</span><span class="k">user</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="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="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="mi">5</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">-- regexp_substr</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">regexp_substr</span><span class="p">(</span><span class="s1">'Steven Jones and Stephen Smith are the best players'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Ste(v|ph)en'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_substr</span><span class="p">(</span><span class="n">Steven</span><span class="w"> </span><span class="n">Jones</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="n">Stephen</span><span class="w"> </span><span class="n">Smith</span><span class="w"> </span><span class="k">are</span><span class="w"> </span><span class="n">the</span><span class="w"> </span><span class="n">best</span><span class="w"> </span><span class="n">players</span><span class="p">,</span><span class="w"> </span><span class="n">Ste</span><span class="p">(</span><span class="n">v</span><span class="o">|</span><span class="n">ph</span><span class="p">)</span><span class="n">en</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">Steven</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">regexp_substr</span><span class="p">(</span><span class="s1">'Steven Jones and Stephen Smith are the best players'</span><span class="p">,</span><span class="w"> </span><span class="s1">'Jeck'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="n">regexp_substr</span><span class="p">(</span><span class="n">Steven</span><span class="w"> </span><span class="n">Jones</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="n">Stephen</span><span class="w"> </span><span class="n">Smith</span><span class="w"> </span><span class="k">are</span><span class="w"> </span><span class="n">the</span><span class="w"> </span><span class="n">best</span><span class="w"> </span><span class="n">players</span><span class="p">,</span><span class="w"> </span><span class="n">Jeck</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">-- 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="k">SELECT</span><span class="w"> </span><span class="n">substr</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="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">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="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="p">[</span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">20</span><span class="w"> </span><span class="mi">53</span><span class="w"> </span><span class="mi">51</span><span class="w"> </span><span class="mi">4</span><span class="k">C</span><span class="p">]</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="k">SELECT</span><span class="w"> </span><span class="k">substring</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="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">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="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="p">[</span><span class="mi">6</span><span class="n">B</span><span class="w"> </span><span class="mi">20</span><span class="w"> </span><span class="mi">53</span><span class="w"> </span><span class="mi">51</span><span class="w"> </span><span class="mi">4</span><span class="k">C</span><span class="p">]</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_char</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_char</span><span class="p">(</span><span class="mi">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_char</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_char</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="s1">'000D00'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</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="n">D00</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_char</span><span class="p">(</span><span class="mi">12454</span><span class="p">,</span><span class="w"> </span><span class="s1">'99G999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</span><span class="mi">12454</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="n">G999</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">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_char</span><span class="p">(</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="s1">'$99.99'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</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="err">$</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_char</span><span class="p">(</span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="p">,</span><span class="w"> </span><span class="s1">'99G999D9S'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="n">G999D9S</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">454</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">-- 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">-- to_varchar</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">to_varchar</span><span class="p">(</span><span class="mi">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_char</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_varchar</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="s1">'000D00'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</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="n">D00</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_varchar</span><span class="p">(</span><span class="mi">12454</span><span class="p">,</span><span class="w"> </span><span class="s1">'99G999'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</span><span class="mi">12454</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="n">G999</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">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_varchar</span><span class="p">(</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="s1">'$99.99'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</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="err">$</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_varchar</span><span class="p">(</span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="p">,</span><span class="w"> </span><span class="s1">'99G999D9S'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------------+</span> |
| <span class="o">|</span><span class="n">to_char</span><span class="p">(</span><span class="o">-</span><span class="mi">12454</span><span class="p">.</span><span class="mi">8</span><span class="p">,</span><span class="w"> </span><span class="mi">99</span><span class="n">G999D9S</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">454</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> |
