| <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">-- collate</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">COLLATION</span><span class="p">(</span><span class="s1">'Spark SQL'</span><span class="w"> </span><span class="k">collate</span><span class="w"> </span><span class="n">UTF8_LCASE</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------+</span> |
| <span class="o">|</span><span class="k">collation</span><span class="p">(</span><span class="k">collate</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">UTF8_LCASE</span><span class="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">SYSTEM</span><span class="p">.</span><span class="n">BUILTIN</span><span class="p">.</span><span class="n">UT</span><span class="p">...</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-----------------------------------------+</span> |
| |
| <span class="c1">-- collation</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="k">collation</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">collation</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="k">SYSTEM</span><span class="p">.</span><span class="n">BUILTIN</span><span class="p">.</span><span class="n">UT</span><span class="p">...</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">-- is_valid_utf8</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">is_valid_utf8</span><span class="p">(</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">is_valid_utf8</span><span class="p">(</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">is_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'61'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| <span class="o">|</span><span class="n">is_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'61'</span><span class="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">is_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'80'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| <span class="o">|</span><span class="n">is_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'80'</span><span class="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">is_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'61C262'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">------------------------+</span> |
| <span class="o">|</span><span class="n">is_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'61C262'</span><span class="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">-- 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">-- make_valid_utf8</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">make_valid_utf8</span><span class="p">(</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">make_valid_utf8</span><span class="p">(</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="n">Spark</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">make_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'61'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">make_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'61'</span><span class="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="k">SELECT</span><span class="w"> </span><span class="n">make_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'80'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| <span class="o">|</span><span class="n">make_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'80'</span><span class="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="o">|</span> |
| <span class="o">+</span><span class="c1">----------------------+</span> |
| |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">make_valid_utf8</span><span class="p">(</span><span class="n">x</span><span class="s1">'61C262'</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">--------------------------+</span> |
| <span class="o">|</span><span class="n">make_valid_utf8</span><span class="p">(</span><span class="n">X</span><span class="s1">'61C262'</span><span class="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="err">�</span><span class="n">b</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">-- quote</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">quote</span><span class="p">(</span><span class="s1">'Don\'</span><span class="n">t</span><span class="s1">');</span> |
| <span class="s1">+------------+</span> |
| <span class="s1">|quote(Don'</span><span class="n">t</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="s1">'Don\'</span><span class="n">t</span><span class="s1">'|</span> |
| <span class="s1">+------------+</span> |
| |
| <span class="s1">-- randstr</span> |
| <span class="s1">SELECT randstr(3, 0) AS result;</span> |
| <span class="s1">+------+</span> |
| <span class="s1">|result|</span> |
| <span class="s1">+------+</span> |
| <span class="s1">| ceV|</span> |
| <span class="s1">+------+</span> |
| |
| <span class="s1">-- regexp_count</span> |
| <span class="s1">SELECT regexp_count('</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="s1">', '</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="s1">');</span> |
| <span class="s1">+------------------------------------------------------------------------------+</span> |
| <span class="s1">|regexp_count(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|</span> |
| <span class="s1">+------------------------------------------------------------------------------+</span> |
| <span class="s1">| 2|</span> |
| <span class="s1">+------------------------------------------------------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_count('</span><span class="n">abcdefghijklmnopqrstuvwxyz</span><span class="s1">', '</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="s1">');</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| <span class="s1">|regexp_count(abcdefghijklmnopqrstuvwxyz, [a-z]{3})|</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| <span class="s1">| 8|</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| |
| <span class="s1">-- regexp_extract</span> |
| <span class="s1">SELECT regexp_extract('</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="s1">', '</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="s1">', 1);</span> |
| <span class="s1">+---------------------------------------+</span> |
| <span class="s1">|regexp_extract(100-200, (\d+)-(\d+), 1)|</span> |
| <span class="s1">+---------------------------------------+</span> |
| <span class="s1">| 100|</span> |
| <span class="s1">+---------------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_extract('</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="s1">', r'</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="s1">', 1);</span> |
| <span class="s1">+---------------------------------------+</span> |
| <span class="s1">|regexp_extract(100-200, (\d+)-(\d+), 1)|</span> |
| <span class="s1">+---------------------------------------+</span> |
| <span class="s1">| 100|</span> |
| <span class="s1">+---------------------------------------+</span> |
| |
| <span class="s1">-- regexp_extract_all</span> |
| <span class="s1">SELECT regexp_extract_all('</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="s1">', '</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="s1">', 1);</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| <span class="s1">|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| <span class="s1">| [100, 300]|</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_extract_all('</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="s1">', r'</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="s1">', 1);</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| <span class="s1">|regexp_extract_all(100-200, 300-400, (\d+)-(\d+), 1)|</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| <span class="s1">| [100, 300]|</span> |
| <span class="s1">+----------------------------------------------------+</span> |
| |
| <span class="s1">-- regexp_instr</span> |
| <span class="s1">SELECT regexp_instr(r"\abc", r"^\\abc$");</span> |
| <span class="s1">+------------------------------+</span> |
| <span class="s1">|regexp_instr(\abc, ^\\abc$, 0)|</span> |
| <span class="s1">+------------------------------+</span> |
| <span class="s1">| 1|</span> |
| <span class="s1">+------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_instr('</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="s1">', '</span><span class="o">@</span><span class="p">[</span><span class="o">^</span><span class="p">.]</span><span class="o">*</span><span class="s1">');</span> |
| <span class="s1">+----------------------------------------------+</span> |
| <span class="s1">|regexp_instr(user@spark.apache.org, @[^.]*, 0)|</span> |
| <span class="s1">+----------------------------------------------+</span> |
| <span class="s1">| 5|</span> |
| <span class="s1">+----------------------------------------------+</span> |
| |
| <span class="s1">-- regexp_replace</span> |
| <span class="s1">SELECT regexp_replace('</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="s1">', '</span><span class="p">(</span><span class="err">\\</span><span class="n">d</span><span class="o">+</span><span class="p">)</span><span class="s1">', '</span><span class="n">num</span><span class="s1">');</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">|regexp_replace(100-200, (\d+), num, 1)|</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">| num-num|</span> |
| <span class="s1">+--------------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_replace('</span><span class="mi">100</span><span class="o">-</span><span class="mi">200</span><span class="s1">', r'</span><span class="p">(</span><span class="err">\</span><span class="n">d</span><span class="o">+</span><span class="p">)</span><span class="s1">', '</span><span class="n">num</span><span class="s1">');</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">|regexp_replace(100-200, (\d+), num, 1)|</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">| num-num|</span> |
| <span class="s1">+--------------------------------------+</span> |
| |
| <span class="s1">-- regexp_substr</span> |
| <span class="s1">SELECT regexp_substr('</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="s1">', '</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="s1">');</span> |
| <span class="s1">+-------------------------------------------------------------------------------+</span> |
| <span class="s1">|regexp_substr(Steven Jones and Stephen Smith are the best players, Ste(v|ph)en)|</span> |
| <span class="s1">+-------------------------------------------------------------------------------+</span> |
| <span class="s1">| Steven|</span> |
| <span class="s1">+-------------------------------------------------------------------------------+</span> |
| |
| <span class="s1">SELECT regexp_substr('</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="s1">', '</span><span class="n">Jeck</span><span class="s1">');</span> |
| <span class="s1">+------------------------------------------------------------------------+</span> |
| <span class="s1">|regexp_substr(Steven Jones and Stephen Smith are the best players, Jeck)|</span> |
| <span class="s1">+------------------------------------------------------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+------------------------------------------------------------------------+</span> |
| |
| <span class="s1">-- repeat</span> |
| <span class="s1">SELECT repeat('</span><span class="mi">123</span><span class="s1">', 2);</span> |
| <span class="s1">+--------------+</span> |
| <span class="s1">|repeat(123, 2)|</span> |
| <span class="s1">+--------------+</span> |
| <span class="s1">| 123123|</span> |
| <span class="s1">+--------------+</span> |
| |
| <span class="s1">-- replace</span> |
| <span class="s1">SELECT replace('</span><span class="n">ABCabc</span><span class="s1">', '</span><span class="n">abc</span><span class="s1">', '</span><span class="n">DEF</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|replace(ABCabc, abc, DEF)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| ABCDEF|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">-- right</span> |
| <span class="s1">SELECT right('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', 3);</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">|right(Spark SQL, 3)|</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">| SQL|</span> |
| <span class="s1">+-------------------+</span> |
| |
| <span class="s1">-- rpad</span> |
| <span class="s1">SELECT rpad('</span><span class="n">hi</span><span class="s1">', 5, '</span><span class="o">??</span><span class="s1">');</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">|rpad(hi, 5, ??)|</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">| hi???|</span> |
| <span class="s1">+---------------+</span> |
| |
| <span class="s1">SELECT rpad('</span><span class="n">hi</span><span class="s1">', 1, '</span><span class="o">??</span><span class="s1">');</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">|rpad(hi, 1, ??)|</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">| h|</span> |
| <span class="s1">+---------------+</span> |
| |
| <span class="s1">SELECT rpad('</span><span class="n">hi</span><span class="s1">', 5);</span> |
| <span class="s1">+--------------+</span> |
| <span class="s1">|rpad(hi, 5, )|</span> |
| <span class="s1">+--------------+</span> |
| <span class="s1">| hi |</span> |
| <span class="s1">+--------------+</span> |
| |
| <span class="s1">SELECT hex(rpad(unhex('</span><span class="n">aabb</span><span class="s1">'), 5));</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">|hex(rpad(unhex(aabb), 5, X'</span><span class="mi">00</span><span class="s1">'))|</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">| AABB000000|</span> |
| <span class="s1">+--------------------------------+</span> |
| |
| <span class="s1">SELECT hex(rpad(unhex('</span><span class="n">aabb</span><span class="s1">'), 5, unhex('</span><span class="mi">1122</span><span class="s1">')));</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">|hex(rpad(unhex(aabb), 5, unhex(1122)))|</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">| AABB112211|</span> |
| <span class="s1">+--------------------------------------+</span> |
| |
| <span class="s1">-- rtrim</span> |
| <span class="s1">SELECT rtrim('</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|rtrim( SparkSQL )|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| SparkSQL|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">-- sentences</span> |
| <span class="s1">SELECT sentences('</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="s1">');</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">|sentences(Hi there! Good morning., , )|</span> |
| <span class="s1">+--------------------------------------+</span> |
| <span class="s1">| [[Hi, there], [Go...|</span> |
| <span class="s1">+--------------------------------------+</span> |
| |
| <span class="s1">SELECT sentences('</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="s1">', '</span><span class="n">en</span><span class="s1">');</span> |
| <span class="s1">+----------------------------------------+</span> |
| <span class="s1">|sentences(Hi there! Good morning., en, )|</span> |
| <span class="s1">+----------------------------------------+</span> |
| <span class="s1">| [[Hi, there], [Go...|</span> |
| <span class="s1">+----------------------------------------+</span> |
| |
| <span class="s1">SELECT sentences('</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="s1">', '</span><span class="n">en</span><span class="s1">', '</span><span class="n">US</span><span class="s1">');</span> |
| <span class="s1">+------------------------------------------+</span> |
| <span class="s1">|sentences(Hi there! Good morning., en, US)|</span> |
| <span class="s1">+------------------------------------------+</span> |
| <span class="s1">| [[Hi, there], [Go...|</span> |
| <span class="s1">+------------------------------------------+</span> |
| |
| <span class="s1">-- soundex</span> |
| <span class="s1">SELECT soundex('</span><span class="n">Miller</span><span class="s1">');</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">|soundex(Miller)|</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">| M460|</span> |
| <span class="s1">+---------------+</span> |
| |
| <span class="s1">-- space</span> |
| <span class="s1">SELECT concat(space(2), '</span><span class="mi">1</span><span class="s1">');</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">|concat(space(2), 1)|</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">| 1|</span> |
| <span class="s1">+-------------------+</span> |
| |
| <span class="s1">-- split</span> |
| <span class="s1">SELECT split('</span><span class="n">oneAtwoBthreeC</span><span class="s1">', '</span><span class="p">[</span><span class="n">ABC</span><span class="p">]</span><span class="s1">');</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">|split(oneAtwoBthreeC, [ABC], -1)|</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">| [one, two, three, ]|</span> |
| <span class="s1">+--------------------------------+</span> |
| |
| <span class="s1">SELECT split('</span><span class="n">oneAtwoBthreeC</span><span class="s1">', '</span><span class="p">[</span><span class="n">ABC</span><span class="p">]</span><span class="s1">', -1);</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">|split(oneAtwoBthreeC, [ABC], -1)|</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">| [one, two, three, ]|</span> |
| <span class="s1">+--------------------------------+</span> |
| |
| <span class="s1">SELECT split('</span><span class="n">oneAtwoBthreeC</span><span class="s1">', '</span><span class="p">[</span><span class="n">ABC</span><span class="p">]</span><span class="s1">', 2);</span> |
| <span class="s1">+-------------------------------+</span> |
| <span class="s1">|split(oneAtwoBthreeC, [ABC], 2)|</span> |
| <span class="s1">+-------------------------------+</span> |
| <span class="s1">| [one, twoBthreeC]|</span> |
| <span class="s1">+-------------------------------+</span> |
| |
| <span class="s1">-- split_part</span> |
| <span class="s1">SELECT split_part('</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="s1">', '</span><span class="p">.</span><span class="s1">', 3);</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|split_part(11.12.13, ., 3)|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| 13|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">-- startswith</span> |
| <span class="s1">SELECT startswith('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', '</span><span class="n">Spark</span><span class="s1">');</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">|startswith(Spark SQL, Spark)|</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">| true|</span> |
| <span class="s1">+----------------------------+</span> |
| |
| <span class="s1">SELECT startswith('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', '</span><span class="k">SQL</span><span class="s1">');</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|startswith(Spark SQL, SQL)|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| false|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">SELECT startswith('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', null);</span> |
| <span class="s1">+---------------------------+</span> |
| <span class="s1">|startswith(Spark SQL, NULL)|</span> |
| <span class="s1">+---------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+---------------------------+</span> |
| |
| <span class="s1">SELECT startswith(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', x'</span><span class="mi">537061726</span><span class="n">b</span><span class="s1">');</span> |
| <span class="s1">+------------------------------------------------+</span> |
| <span class="s1">|startswith(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">', X'</span><span class="mi">537061726</span><span class="n">B</span><span class="s1">')|</span> |
| <span class="s1">+------------------------------------------------+</span> |
| <span class="s1">| true|</span> |
| <span class="s1">+------------------------------------------------+</span> |
| |
| <span class="s1">SELECT startswith(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', x'</span><span class="mi">53514</span><span class="k">c</span><span class="s1">');</span> |
| <span class="s1">+--------------------------------------------+</span> |
| <span class="s1">|startswith(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">', X'</span><span class="mi">53514</span><span class="k">C</span><span class="s1">')|</span> |
| <span class="s1">+--------------------------------------------+</span> |
| <span class="s1">| false|</span> |
| <span class="s1">+--------------------------------------------+</span> |
| |
| <span class="s1">-- substr</span> |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', 5);</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">|substr(Spark SQL, 5, 2147483647)|</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">| k SQL|</span> |
| <span class="s1">+--------------------------------+</span> |
| |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', -3);</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|substr(Spark SQL, -3, 2147483647)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| SQL|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', 5, 1);</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">|substr(Spark SQL, 5, 1)|</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">| k|</span> |
| <span class="s1">+-----------------------+</span> |
| |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM 5);</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 2147483647)|</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">| k SQL|</span> |
| <span class="s1">+-----------------------------------+</span> |
| |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM -3);</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, -3, 2147483647)|</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">| SQL|</span> |
| <span class="s1">+------------------------------------+</span> |
| |
| <span class="s1">SELECT substr('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM 5 FOR 1);</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 1)|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| k|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">SELECT substr(encode('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">'), 5);</span> |
| <span class="s1">+-----------------------------------------------+</span> |
| <span class="s1">|substr(encode(Spark SQL, utf-8), 5, 2147483647)|</span> |
| <span class="s1">+-----------------------------------------------+</span> |
| <span class="s1">| [6B 20 53 51 4C]|</span> |
| <span class="s1">+-----------------------------------------------+</span> |
| |
| <span class="s1">-- substring</span> |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', 5);</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 2147483647)|</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">| k SQL|</span> |
| <span class="s1">+-----------------------------------+</span> |
| |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', -3);</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, -3, 2147483647)|</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">| SQL|</span> |
| <span class="s1">+------------------------------------+</span> |
| |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', 5, 1);</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 1)|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| k|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM 5);</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 2147483647)|</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">| k SQL|</span> |
| <span class="s1">+-----------------------------------+</span> |
| |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM -3);</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">|substring(Spark SQL, -3, 2147483647)|</span> |
| <span class="s1">+------------------------------------+</span> |
| <span class="s1">| SQL|</span> |
| <span class="s1">+------------------------------------+</span> |
| |
| <span class="s1">SELECT substring('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">' FROM 5 FOR 1);</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|substring(Spark SQL, 5, 1)|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| k|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">SELECT substring(encode('</span><span class="n">Spark</span><span class="w"> </span><span class="k">SQL</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">'), 5);</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| <span class="s1">|substring(encode(Spark SQL, utf-8), 5, 2147483647)|</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| <span class="s1">| [6B 20 53 51 4C]|</span> |
| <span class="s1">+--------------------------------------------------+</span> |
| |
| <span class="s1">-- substring_index</span> |
| <span class="s1">SELECT substring_index('</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="s1">', '</span><span class="p">.</span><span class="s1">', 2);</span> |
| <span class="s1">+-------------------------------------+</span> |
| <span class="s1">|substring_index(www.apache.org, ., 2)|</span> |
| <span class="s1">+-------------------------------------+</span> |
| <span class="s1">| www.apache|</span> |
| <span class="s1">+-------------------------------------+</span> |
| |
| <span class="s1">-- to_binary</span> |
| <span class="s1">SELECT to_binary('</span><span class="n">abc</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">');</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">|to_binary(abc, utf-8)|</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">| [61 62 63]|</span> |
| <span class="s1">+---------------------+</span> |
| |
| <span class="s1">-- to_char</span> |
| <span class="s1">SELECT to_char(454, '</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-----------------+</span> |
| <span class="s1">|to_char(454, 999)|</span> |
| <span class="s1">+-----------------+</span> |
| <span class="s1">| 454|</span> |
| <span class="s1">+-----------------+</span> |
| |
| <span class="s1">SELECT to_char(454.00, '</span><span class="mi">000</span><span class="n">D00</span><span class="s1">');</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">|to_char(454.00, 000D00)|</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">| 454.00|</span> |
| <span class="s1">+-----------------------+</span> |
| |
| <span class="s1">SELECT to_char(12454, '</span><span class="mi">99</span><span class="n">G999</span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|to_char(12454, 99G999)|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| 12,454|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT to_char(78.12, '</span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|to_char(78.12, $99.99)|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| $78.12|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT to_char(-12454.8, '</span><span class="mi">99</span><span class="n">G999D9S</span><span class="s1">');</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">|to_char(-12454.8, 99G999D9S)|</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">| 12,454.8-|</span> |
| <span class="s1">+----------------------------+</span> |
| |
| <span class="s1">SELECT to_char(date'</span><span class="mi">2016</span><span class="o">-</span><span class="mi">04</span><span class="o">-</span><span class="mi">08</span><span class="s1">', '</span><span class="n">y</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|date_format(DATE '</span><span class="mi">2016</span><span class="o">-</span><span class="mi">04</span><span class="o">-</span><span class="mi">08</span><span class="s1">', y)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| 2016|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">SELECT to_char(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', '</span><span class="n">base64</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|base64(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">')|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| U3BhcmsgU1FM|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT to_char(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', '</span><span class="n">hex</span><span class="s1">');</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">|hex(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">')|</span> |
| <span class="s1">+--------------------------+</span> |
| <span class="s1">| 537061726B2053514C|</span> |
| <span class="s1">+--------------------------+</span> |
| |
| <span class="s1">SELECT to_char(encode('</span><span class="n">abc</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">'), '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|decode(encode(abc, utf-8), utf-8)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| abc|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">-- to_number</span> |
| <span class="s1">SELECT to_number('</span><span class="mi">454</span><span class="s1">', '</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">|to_number(454, 999)|</span> |
| <span class="s1">+-------------------+</span> |
| <span class="s1">| 454|</span> |
| <span class="s1">+-------------------+</span> |
| |
| <span class="s1">SELECT to_number('</span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="s1">', '</span><span class="mi">000</span><span class="p">.</span><span class="mi">00</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|to_number(454.00, 000.00)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| 454.00|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">SELECT to_number('</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="s1">', '</span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|to_number(12,454, 99,999)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| 12454|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">SELECT to_number('</span><span class="err">$</span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="s1">', '</span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|to_number($78.12, $99.99)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| 78.12|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">SELECT to_number('</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="s1">', '</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="s1">');</span> |
| <span class="s1">+-------------------------------+</span> |
| <span class="s1">|to_number(12,454.8-, 99,999.9S)|</span> |
| <span class="s1">+-------------------------------+</span> |
| <span class="s1">| -12454.8|</span> |
| <span class="s1">+-------------------------------+</span> |
| |
| <span class="s1">-- to_varchar</span> |
| <span class="s1">SELECT to_varchar(454, '</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-----------------+</span> |
| <span class="s1">|to_char(454, 999)|</span> |
| <span class="s1">+-----------------+</span> |
| <span class="s1">| 454|</span> |
| <span class="s1">+-----------------+</span> |
| |
| <span class="s1">SELECT to_varchar(454.00, '</span><span class="mi">000</span><span class="n">D00</span><span class="s1">');</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">|to_char(454.00, 000D00)|</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">| 454.00|</span> |
| <span class="s1">+-----------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(12454, '</span><span class="mi">99</span><span class="n">G999</span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|to_char(12454, 99G999)|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| 12,454|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(78.12, '</span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|to_char(78.12, $99.99)|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| $78.12|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(-12454.8, '</span><span class="mi">99</span><span class="n">G999D9S</span><span class="s1">');</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">|to_char(-12454.8, 99G999D9S)|</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">| 12,454.8-|</span> |
| <span class="s1">+----------------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(date'</span><span class="mi">2016</span><span class="o">-</span><span class="mi">04</span><span class="o">-</span><span class="mi">08</span><span class="s1">', '</span><span class="n">y</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|date_format(DATE '</span><span class="mi">2016</span><span class="o">-</span><span class="mi">04</span><span class="o">-</span><span class="mi">08</span><span class="s1">', y)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| 2016|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', '</span><span class="n">base64</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|to_varchar(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">')|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| U3BhcmsgU1FM|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(x'</span><span class="mi">537061726</span><span class="n">b2053514c</span><span class="s1">', '</span><span class="n">hex</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|to_varchar(X'</span><span class="mi">537061726</span><span class="n">B2053514C</span><span class="s1">')|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| 537061726B2053514C|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">SELECT to_varchar(encode('</span><span class="n">abc</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">'), '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">');</span> |
| <span class="s1">+-------------------------------------+</span> |
| <span class="s1">|to_varchar(encode(abc, utf-8), utf-8)|</span> |
| <span class="s1">+-------------------------------------+</span> |
| <span class="s1">| abc|</span> |
| <span class="s1">+-------------------------------------+</span> |
| |
| <span class="s1">-- translate</span> |
| <span class="s1">SELECT translate('</span><span class="n">AaBbCc</span><span class="s1">', '</span><span class="n">abc</span><span class="s1">', '</span><span class="mi">123</span><span class="s1">');</span> |
| <span class="s1">+---------------------------+</span> |
| <span class="s1">|translate(AaBbCc, abc, 123)|</span> |
| <span class="s1">+---------------------------+</span> |
| <span class="s1">| A1B2C3|</span> |
| <span class="s1">+---------------------------+</span> |
| |
| <span class="s1">-- trim</span> |
| <span class="s1">SELECT trim('</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="s1">');</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">|trim( SparkSQL )|</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">| SparkSQL|</span> |
| <span class="s1">+---------------------+</span> |
| |
| <span class="s1">SELECT trim(BOTH FROM '</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="s1">');</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">|trim( SparkSQL )|</span> |
| <span class="s1">+---------------------+</span> |
| <span class="s1">| SparkSQL|</span> |
| <span class="s1">+---------------------+</span> |
| |
| <span class="s1">SELECT trim(LEADING FROM '</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|ltrim( SparkSQL )|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| SparkSQL |</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT trim(TRAILING FROM '</span><span class="w"> </span><span class="n">SparkSQL</span><span class="w"> </span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|rtrim( SparkSQL )|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| SparkSQL|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">SELECT trim('</span><span class="n">SL</span><span class="s1">' FROM '</span><span class="n">SSparkSQLS</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|TRIM(BOTH SL FROM SSparkSQLS)|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| parkSQ|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT trim(BOTH '</span><span class="n">SL</span><span class="s1">' FROM '</span><span class="n">SSparkSQLS</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|TRIM(BOTH SL FROM SSparkSQLS)|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| parkSQ|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT trim(LEADING '</span><span class="n">SL</span><span class="s1">' FROM '</span><span class="n">SSparkSQLS</span><span class="s1">');</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">|TRIM(LEADING SL FROM SSparkSQLS)|</span> |
| <span class="s1">+--------------------------------+</span> |
| <span class="s1">| parkSQLS|</span> |
| <span class="s1">+--------------------------------+</span> |
| |
| <span class="s1">SELECT trim(TRAILING '</span><span class="n">SL</span><span class="s1">' FROM '</span><span class="n">SSparkSQLS</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|TRIM(TRAILING SL FROM SSparkSQLS)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| SSparkSQ|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">-- try_to_binary</span> |
| <span class="s1">SELECT try_to_binary('</span><span class="n">abc</span><span class="s1">', '</span><span class="n">utf</span><span class="o">-</span><span class="mi">8</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|try_to_binary(abc, utf-8)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| [61 62 63]|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">select try_to_binary('</span><span class="n">a</span><span class="o">!</span><span class="s1">', '</span><span class="n">base64</span><span class="s1">');</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">|try_to_binary(a!, base64)|</span> |
| <span class="s1">+-------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+-------------------------+</span> |
| |
| <span class="s1">select try_to_binary('</span><span class="n">abc</span><span class="s1">', '</span><span class="n">invalidFormat</span><span class="s1">');</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">|try_to_binary(abc, invalidFormat)|</span> |
| <span class="s1">+---------------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+---------------------------------+</span> |
| |
| <span class="s1">-- try_to_number</span> |
| <span class="s1">SELECT try_to_number('</span><span class="mi">454</span><span class="s1">', '</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">|try_to_number(454, 999)|</span> |
| <span class="s1">+-----------------------+</span> |
| <span class="s1">| 454|</span> |
| <span class="s1">+-----------------------+</span> |
| |
| <span class="s1">SELECT try_to_number('</span><span class="mi">454</span><span class="p">.</span><span class="mi">00</span><span class="s1">', '</span><span class="mi">000</span><span class="p">.</span><span class="mi">00</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|try_to_number(454.00, 000.00)|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| 454.00|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT try_to_number('</span><span class="mi">12</span><span class="p">,</span><span class="mi">454</span><span class="s1">', '</span><span class="mi">99</span><span class="p">,</span><span class="mi">999</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|try_to_number(12,454, 99,999)|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| 12454|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT try_to_number('</span><span class="err">$</span><span class="mi">78</span><span class="p">.</span><span class="mi">12</span><span class="s1">', '</span><span class="err">$</span><span class="mi">99</span><span class="p">.</span><span class="mi">99</span><span class="s1">');</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">|try_to_number($78.12, $99.99)|</span> |
| <span class="s1">+-----------------------------+</span> |
| <span class="s1">| 78.12|</span> |
| <span class="s1">+-----------------------------+</span> |
| |
| <span class="s1">SELECT try_to_number('</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="s1">', '</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="s1">');</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">|try_to_number(12,454.8-, 99,999.9S)|</span> |
| <span class="s1">+-----------------------------------+</span> |
| <span class="s1">| -12454.8|</span> |
| <span class="s1">+-----------------------------------+</span> |
| |
| <span class="s1">-- try_validate_utf8</span> |
| <span class="s1">SELECT try_validate_utf8('</span><span class="n">Spark</span><span class="s1">');</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">|try_validate_utf8(Spark)|</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">| Spark|</span> |
| <span class="s1">+------------------------+</span> |
| |
| <span class="s1">SELECT try_validate_utf8(x'</span><span class="mi">61</span><span class="s1">');</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">|try_validate_utf8(X'</span><span class="mi">61</span><span class="s1">')|</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">| a|</span> |
| <span class="s1">+------------------------+</span> |
| |
| <span class="s1">SELECT try_validate_utf8(x'</span><span class="mi">80</span><span class="s1">');</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">|try_validate_utf8(X'</span><span class="mi">80</span><span class="s1">')|</span> |
| <span class="s1">+------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+------------------------+</span> |
| |
| <span class="s1">SELECT try_validate_utf8(x'</span><span class="mi">61</span><span class="n">C262</span><span class="s1">');</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">|try_validate_utf8(X'</span><span class="mi">61</span><span class="n">C262</span><span class="s1">')|</span> |
| <span class="s1">+----------------------------+</span> |
| <span class="s1">| NULL|</span> |
| <span class="s1">+----------------------------+</span> |
| |
| <span class="s1">-- ucase</span> |
| <span class="s1">SELECT ucase('</span><span class="n">SparkSql</span><span class="s1">');</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">|ucase(SparkSql)|</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">| SPARKSQL|</span> |
| <span class="s1">+---------------+</span> |
| |
| <span class="s1">-- unbase64</span> |
| <span class="s1">SELECT unbase64('</span><span class="n">U3BhcmsgU1FM</span><span class="s1">');</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">|unbase64(U3BhcmsgU1FM)|</span> |
| <span class="s1">+----------------------+</span> |
| <span class="s1">| [53 70 61 72 6B 2...|</span> |
| <span class="s1">+----------------------+</span> |
| |
| <span class="s1">-- upper</span> |
| <span class="s1">SELECT upper('</span><span class="n">SparkSql</span><span class="s1">');</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">|upper(SparkSql)|</span> |
| <span class="s1">+---------------+</span> |
| <span class="s1">| SPARKSQL|</span> |
| <span class="s1">+---------------+</span> |
| |
| <span class="s1">-- validate_utf8</span> |
| <span class="s1">SELECT validate_utf8('</span><span class="n">Spark</span><span class="s1">');</span> |
| <span class="s1">+--------------------+</span> |
| <span class="s1">|validate_utf8(Spark)|</span> |
| <span class="s1">+--------------------+</span> |
| <span class="s1">| Spark|</span> |
| <span class="s1">+--------------------+</span> |
| |
| <span class="s1">SELECT validate_utf8(x'</span><span class="mi">61</span><span class="s1">');</span> |
| <span class="s1">+--------------------+</span> |
| <span class="s1">|validate_utf8(X'</span><span class="mi">61</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="n">a</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">--------------------+</span> |
| </code></pre></div> |
