yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
taurusdb
doc-exports/docs/dli/sqlreference/dli_08_15089.html
Su, Xiaomeng be9eabe464 dli_sqlreference_20250305 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Su, Xiaomeng <suxiaomeng1@huawei.com>
Co-committed-by: Su, Xiaomeng <suxiaomeng1@huawei.com>
2025-03-25 09:06:21 +00:00

332 lines
37 KiB
HTML
Raw Permalink Blame History

<a name="dli_08_15089"></a><a name="dli_08_15089"></a>
<h1 class="topictitle1">String Functions</h1>
<div id="body0000001310215805"><div class="section" id="dli_08_15089__section121965441793"><h4 class="sectiontitle">String Functions</h4><p id="dli_08_15089__p1537116251767">DLI offers a wide range of string functions for processing and transforming string data. These functions include concatenation, case conversion, substring extraction, replacement, regex matching, encoding and decoding, format conversion, and more. Additionally, it supports string length calculation, position searching, padding, reversing, and even extracting values from JSON strings using the <strong id="dli_08_15089__b9938162316313">JSON_VAL</strong> function. These features are widely used in data cleansing, text processing, and data analysis scenarios, providing developers with powerful tool support.</p>
<p id="dli_08_15089__p3145685713">For detailed string functions, see <a href="#dli_08_15089__table157276446018">Table 1</a>. For more information, see <a href="https://nightlies.apache.org/flink" target="_blank" rel="noopener noreferrer">Apache Flink</a>.</p>
<div class="tablenoborder"><a name="dli_08_15089__table157276446018"></a><a name="table157276446018"></a><table cellpadding="4" cellspacing="0" summary="" id="dli_08_15089__table157276446018" frame="border" border="1" rules="all"><caption><b>Table 1 </b>String functions</caption><thead align="left"><tr id="dli_08_15089__row37271844501"><th align="left" class="cellrowborder" valign="top" width="28.610000000000003%" id="mcps1.3.1.4.2.3.1.1"><p id="dli_08_15089__p67275447010">SQL Function</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="71.39%" id="mcps1.3.1.4.2.3.1.2"><p id="dli_08_15089__p572814446017">Description</p>
</th>
</tr>
</thead>
<tbody><tr id="dli_08_15089__row572817446015"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p62310522717">string1 || string2</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p2988992716">Returns the concatenation of <strong id="dli_08_15089__b3938164303312">STRING1</strong> and <strong id="dli_08_15089__b12386154543311">STRING2</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row177282443011"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p84681314152713">CHAR_LENGTH(string) CHARACTER_LENGTH(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p2285201916279">Returns the number of characters in a string.</p>
</td>
</tr>
<tr id="dli_08_15089__row1772811447012"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p17281744507">UPPER(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p12885112611270">Returns a string in uppercase.</p>
</td>
</tr>
<tr id="dli_08_15089__row167281944906"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p555012307277">LOWER(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p177289445018">Returns a string in lowercase.</p>
</td>
</tr>
<tr id="dli_08_15089__row372816441301"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1948315519299">POSITION(string1 IN string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1772120148291">Returns the position (starting from 1) of the first occurrence of <strong id="dli_08_15089__b9447161723514">STRING1</strong> in <strong id="dli_08_15089__b15515196352">STRING2</strong>.</p>
<p id="dli_08_15089__p6934101013299">Returns <strong id="dli_08_15089__b1298123917359">0</strong> if <strong id="dli_08_15089__b19626164403518">STRING1</strong> is not found in <strong id="dli_08_15089__b251618461358">STRING2</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row3728104412014"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p78491619162915">TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1516582511292">Returns the result of removing the string that starts/ends/starts and ends with <strong id="dli_08_15089__b18568444384">STRING2</strong> from <strong id="dli_08_15089__b382996113813">STRING1</strong>. By default, both sides' spaces will be removed.</p>
</td>
</tr>
<tr id="dli_08_15089__row1728944201"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p7828123015294">LTRIM(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p7500337122915">Returns the string with left spaces removed from <strong id="dli_08_15089__b1616716506389">STRING</strong>.</p>
<p id="dli_08_15089__p13602103522914">For example, <strong id="dli_08_15089__b17688134416398">' This is a test String.'.ltrim()</strong> returns <strong id="dli_08_15089__b1847719525391">'This is a test String.'</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row972874413017"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p19860141162919">RTRIM(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p73014552298">Returns the string with right spaces removed from <strong id="dli_08_15089__b9197103374012">STRING</strong>.</p>
<p id="dli_08_15089__p1521217312190">For example, <strong id="dli_08_15089__b163471831419">'This is a test String. '.ltrim()</strong> returns <strong id="dli_08_15089__b153496320410">'This is a test String.'</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row27289441503"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p54181316300">REPEAT(string, int)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1185127113015">Returns a string that is the concatenation of INT number of strings.</p>
<p id="dli_08_15089__p1966719610306">For example, <strong id="dli_08_15089__b10876548154216">REPEAT('This is a test String.', 2)</strong> returns <strong id="dli_08_15089__b119211559144217">"This is a test String.This is a test String."</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row67281944704"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p207284441013">REGEXP_REPLACE(string1, string2, string3)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1099016269305">Returns a string where all substrings in <strong id="dli_08_15089__b1672821494420">STRING1</strong> that match the regular expression <strong id="dli_08_15089__b7713141844419">STRING2</strong> are replaced with <strong id="dli_08_15089__b1177832104414">STRING3</strong>.</p>
<p id="dli_08_15089__p3409145416323">For example, <strong id="dli_08_15089__b2497657134417">'foobar'.regexpReplace('oo|ar', '')</strong> returns <strong id="dli_08_15089__b1852262204514">"fb"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row15728124416017"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p2728144904">OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1318124173012">Returns a string that replaces <strong id="dli_08_15089__b5961195619469">INT2</strong> (STRING2's length by default) characters of <strong id="dli_08_15089__b6463924479">STRING1</strong> with <strong id="dli_08_15089__b1240014410473">STRING2</strong> from position <strong id="dli_08_15089__b178731576479">INT1</strong>.</p>
<p id="dli_08_15089__p1097311352710">For example, <strong id="dli_08_15089__b112564116472">'xxxxxtest'.overlay('xxxx', 6)</strong> returns <strong id="dli_08_15089__b92034520476">"xxxxxxxxx"</strong>, and <strong id="dli_08_15089__b165921449124716">'xxxxxtest'.overlay('xxxx', 6, 2)</strong> returns <strong id="dli_08_15089__b334615384718">"xxxxxxxxxst"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row1372812441109"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1454134919309">SUBSTRING(string FROM integer1 [ FOR integer2 ])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p141021954163015">Returns a substring of <strong id="dli_08_15089__b37011027125211">STRING</strong> starting from position <strong id="dli_08_15089__b1765183015522">INT1</strong> with length <strong id="dli_08_15089__b54305329527">INT2</strong> (default to the end).</p>
</td>
</tr>
<tr id="dli_08_15089__row07286441306"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1971005316">REPLACE(string1, string2, string3)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p793113813111">Returns a new string where all occurrences of <strong id="dli_08_15089__b059920105312">STRING2</strong> in <strong id="dli_08_15089__b1466915375318">STRING1</strong> are replaced with <strong id="dli_08_15089__b9176110195310">STRING3</strong> (non-overlapping).</p>
<p id="dli_08_15089__p974416583111">For example, <strong id="dli_08_15089__b11417154718539">'hello world'.replace('world', 'flink')</strong> returns <strong id="dli_08_15089__b1685315275317">'hello flink'</strong>; <strong id="dli_08_15089__b151516185417">'ababab'.replace('abab', 'z')</strong> returns <strong id="dli_08_15089__b012916165413">'zab'</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row187281944704"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p10728124416019">REGEXP_EXTRACT(string1, string2[, integer])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p1311424173117">Splits the string <strong id="dli_08_15089__b18703173314551">STRING1</strong> according to the regular expression rule <strong id="dli_08_15089__b12211339165516">STRING2</strong> and returns the string at the specified position <strong id="dli_08_15089__b10938104765515">INTEGER1</strong>.</p>
<p id="dli_08_15089__p216822643117">The regular expression match group index starts at 1, with 0 indicating the entire regular expression match. In addition, the regular expression match group index should not exceed the defined number of groups.</p>
<p id="dli_08_15089__p032416246493">For example, <strong id="dli_08_15089__b421925025615">REGEXP_EXTRACT('foothebar', 'foo(.*?)(bar)', 2)</strong> returns <strong id="dli_08_15089__b3200554165620">"bar"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row9728164419015"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p5728044401">INITCAP(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p67281544209">Returns a new string where the first character of each word is capitalized and the rest are lowercase. Here, a word is defined as a sequence of alphanumeric characters.</p>
</td>
</tr>
<tr id="dli_08_15089__row1872834415020"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p117286441203">CONCAT(string1, string2, ...)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p818745773117">Returns a string that concatenates string1, string2, ..., together. If any parameter is <strong id="dli_08_15089__b1268620241306">NULL</strong>, <strong id="dli_08_15089__b1385443115011">NULL</strong> is returned.</p>
<p id="dli_08_15089__p93901550313">For example, <strong id="dli_08_15089__b6538174911016">CONCAT('AA', 'BB', 'CC')</strong> returns <strong id="dli_08_15089__b555410530020">"AABBCC"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row372816441014"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1655819114327">CONCAT_WS(string1, string2, string3, ...)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p636918963220">Returns a string that concatenates <strong id="dli_08_15089__b1573619504116">STRING2</strong>, <strong id="dli_08_15089__b3599452210">STRING3</strong>, ..., together with the separator <strong id="dli_08_15089__b112117541811">STRING1</strong>.</p>
<p id="dli_08_15089__p9319201119329">A separator is added between each string to be concatenated.</p>
<p id="dli_08_15089__p03569138325">If <strong id="dli_08_15089__b154801107217">STRING1</strong> is <strong id="dli_08_15089__b277212121620">NULL</strong>, <strong id="dli_08_15089__b167118167218">NULL</strong> is returned.</p>
<p id="dli_08_15089__p198071543210">Compared to <strong id="dli_08_15089__b9566830121">concat()</strong>, <strong id="dli_08_15089__b231319354218">concat_ws()</strong> automatically skips NULL parameters.</p>
<p id="dli_08_15089__p1557118715328">For example, <strong id="dli_08_15089__b18321614315">concat_ws('~', 'AA', Null(STRING), 'BB', '', 'CC')</strong> returns <strong id="dli_08_15089__b1794716101334">"AA~BB~~CC"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row07281344400"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p9728184411019">LPAD(string1, integer, string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p159133443216">Returns a new string where <strong id="dli_08_15089__b12619015615">string2</strong> is left-padded to the length of <strong id="dli_08_15089__b15916346853">INT</strong>.</p>
<p id="dli_08_15089__p85491435143218">If the length of <strong id="dli_08_15089__b14681131164">string1</strong> is less than the value of <strong id="dli_08_15089__b49451555757">INT</strong>, <strong id="dli_08_15089__b10638885613">string1</strong> is returned shortened to an integer character.</p>
<p id="dli_08_15089__p026163213213">For example, <strong id="dli_08_15089__b141743202616">LPAD('hi', 4, '??')</strong> returns <strong id="dli_08_15089__b1280122515619">"??hi"</strong>; <strong id="dli_08_15089__b13205229762">LPAD('hi', 1, '??')</strong> returns <strong id="dli_08_15089__b1584520311667">"h"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row167281644204"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p147283441400">RPAD(string1, integer, string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p13205558183315">Returns a new string where <strong id="dli_08_15089__b12876050868">string2</strong> is right-padded to the length of <strong id="dli_08_15089__b19878155012610">INT</strong>.</p>
<p id="dli_08_15089__p11170601343">If the length of <strong id="dli_08_15089__b61711198719">string1</strong> is less than the value of <strong id="dli_08_15089__b6231919672">INT</strong>, returns a new string where <strong id="dli_08_15089__b173856518811">string1</strong> is shortened to a length of <strong id="dli_08_15089__b64651287813">INT</strong>.</p>
<p id="dli_08_15089__p17445125663318">For example, <strong id="dli_08_15089__b141022211812">RPAD('hi', 4, '??')</strong> returns <strong id="dli_08_15089__b14121422782">"hi??"</strong>; <strong id="dli_08_15089__b614132213820">RPAD('hi', 1, '??')</strong> returns <strong id="dli_08_15089__b5168221486">"h"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row14728134415016"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p117287441401">FROM_BASE64(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p123717128358">Returns the result of decoding the base64-encoded <strong id="dli_08_15089__b94731540292">string1</strong>. If the string is <strong id="dli_08_15089__b844104311918">NULL</strong>, <strong id="dli_08_15089__b479144518912">NULL</strong> is returned.</p>
<p id="dli_08_15089__p16453111084217">For example, <strong id="dli_08_15089__b1289013586915">FROM_BASE64('aGVsbG8gd29ybGQ=')</strong> returns <strong id="dli_08_15089__b83520101017">"hello world"</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row10728164416017"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p167287441404">TO_BASE64(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p10174112553516">Returns the result of encoding the string to base64. If the string is <strong id="dli_08_15089__b2059443341314">NULL</strong>, <strong id="dli_08_15089__b18875123581317">NULL</strong> is returned.</p>
<p id="dli_08_15089__p162871644184312">For example, <strong id="dli_08_15089__b1270104317139">TO_BASE64('hello world')</strong> returns <strong id="dli_08_15089__b1332994716139">"aGVsbG8gd29ybGQ="</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row672884415015"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p37288441012">ASCII(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p61427382351">Returns the numeric value of the first character in the string. If the string is <strong id="dli_08_15089__b1284085013146">NULL</strong>, <strong id="dli_08_15089__b10745458151413">NULL</strong> is returned.</p>
<p id="dli_08_15089__p794473418358">For example, <strong id="dli_08_15089__b16107101013152">ascii('abc')</strong> returns <strong id="dli_08_15089__b148881261513">97</strong>, and <strong id="dli_08_15089__b1510771611517">ascii(CAST(NULL AS VARCHAR))</strong> returns <strong id="dli_08_15089__b1225761801519">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row117283443010"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p13522164313510">CHR(integer)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p10604105143519">Returns the ASCII character that corresponds to the binary value of the <strong id="dli_08_15089__b1552915361169">integer</strong>.</p>
<p id="dli_08_15089__p4563115463519">If the integer is greater than 255, we first take the modulo of the integer with 255 and return the CHR of the modulo.</p>
<p id="dli_08_15089__p731557183516">If the integer is <strong id="dli_08_15089__b1961023191715">NULL</strong>,<strong id="dli_08_15089__b1511119274174"> NULL</strong> is returned.</p>
<p id="dli_08_15089__p0277124953519">For example, <strong id="dli_08_15089__b462314314171">chr(97) returns 'a', chr(353)</strong> returns <strong id="dli_08_15089__b679134731714">'a'</strong>, and <strong id="dli_08_15089__b89837511170">chr(CAST(NULL AS VARCHAR))</strong> returns <strong id="dli_08_15089__b38781546173">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row127281644306"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p131625617367">DECODE(binary, string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p15728161014364">Decodes using the provided character set ('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If any of the parameters are empty, the result will also be empty.</p>
</td>
</tr>
<tr id="dli_08_15089__row3729144418012"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p11911141912369">ENCODE(string1, string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p129122783616">Encodes using the provided character set ('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If any of the parameters are empty, the result will also be empty.</p>
</td>
</tr>
<tr id="dli_08_15089__row11729144419019"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p652211334360">INSTR(string1, string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p9218339153614">Returns the position of the first occurrence of <strong id="dli_08_15089__b116276322231">string2</strong> in <strong id="dli_08_15089__b5651113411234">string1</strong>. Returns <strong id="dli_08_15089__b14166749124314">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b11168124918437">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row187295441709"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p78021050123612">LEFT(string, integer)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p8358115613364">Returns the leftmost substring of the string with a length equal to the <strong id="dli_08_15089__b1157318820256">integer</strong> value. If the <strong id="dli_08_15089__b194311917122511">integer</strong> is negative, an empty string is returned. Returns <strong id="dli_08_15089__b86169443430">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b19617344194320">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row177294441503"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p0729164418012">RIGHT(string, integer)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p15492187103713">Returns the rightmost substring of the string with a length equal to the <strong id="dli_08_15089__b051604412256">integer</strong> value. If the <strong id="dli_08_15089__b17545823172512">integer</strong> is negative, an empty string is returned. Returns <strong id="dli_08_15089__b4551138184314">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b65673817439">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row1172915445013"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p14729164412019">LOCATE(string1, string2[, integer])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p13685131910376">Returns the position of the first occurrence of <strong id="dli_08_15089__b138361151428">string1</strong> after position <strong id="dli_08_15089__b165131912114217">integer</strong> in <strong id="dli_08_15089__b1036861411427">string2</strong>. If not found, returns <strong id="dli_08_15089__b433213301424">0</strong>. Returns <strong id="dli_08_15089__b12783138184218">NULL</strong> if either parameter is <strong id="dli_08_15089__b1978233824217">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row07296445012"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p27295447014">PARSE_URL(string1, string2[, string3])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p9702155423714">Returns a specified part from a URL. The valid values for <strong id="dli_08_15089__b6121949151219">string2</strong> include <strong id="dli_08_15089__b21422191317">"HOST"</strong>, <strong id="dli_08_15089__b937410711134">"PATH"</strong>, <strong id="dli_08_15089__b1676181117138">"QUERY"</strong>, <strong id="dli_08_15089__b11301191517134">"REF"</strong>, <strong id="dli_08_15089__b10408122011132">"PROTOCOL"</strong>, <strong id="dli_08_15089__b19170202616134">"AUTHORITY"</strong>, <strong id="dli_08_15089__b811214301132">"FILE"</strong>, and <strong id="dli_08_15089__b12981534111315">"USERINFO"</strong>.</p>
<p id="dli_08_15089__p651125663715">Returns <strong id="dli_08_15089__b29361051131314">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b17945185110134">NULL</strong>.</p>
<p id="dli_08_15089__p145791658173711">For example, <strong id="dli_08_15089__b102652911420">parse_url('http://facebook.com/path1/p.php?k1=v1&amp;k2=v2#Ref1', 'HOST')</strong> returns <strong id="dli_08_15089__b660810147147">'facebook.com'</strong>.</p>
<p id="dli_08_15089__p10270191183818">You can also extract the value of a specific key in the QUERY by providing a keyword <strong id="dli_08_15089__b232911467147">string3</strong> as the third parameter.</p>
<p id="dli_08_15089__p559504218372">For example, <strong id="dli_08_15089__b8400113157">parse_url('http://facebook.com/path1/p.php?k1=v1&amp;k2=v2#Ref1', 'QUERY', 'k1')</strong> returns <strong id="dli_08_15089__b119941361520">'v1'</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row1672910447020"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p209803281388">REGEXP(string1, string2)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p772714346388">Returns <strong id="dli_08_15089__b2959131761610">TRUE</strong> if any (possibly empty) substring of <strong id="dli_08_15089__b1880722815167">string1</strong> matches the Java regular expression <strong id="dli_08_15089__b17760133519168">string2</strong>, otherwise it returns <strong id="dli_08_15089__b220994191617">FALSE</strong>. Returns <strong id="dli_08_15089__b195191903172">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b7520190101711">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row272915447012"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p14177123223916">REVERSE(string)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p142761037113911">Returns the reversed string. If the string is <strong id="dli_08_15089__b9181183412170">NULL</strong>, returns <strong id="dli_08_15089__b8558337141714">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row1972918444018"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1796084253916">SPLIT_INDEX(string1, string2, integer1)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p280184819396">Splits <strong id="dli_08_15089__b264212539189">string1</strong> by the delimiter <strong id="dli_08_15089__b8504195815185">string2</strong> and returns the integer-th (starting from zero) split string. If the integer is negative, returns <strong id="dli_08_15089__b114731525111919">NULL</strong>. Returns <strong id="dli_08_15089__b2445336141813">NULL</strong> if the value of any parameter is <strong id="dli_08_15089__b19447123619181">NULL</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row1872915441908"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1972915446018">STR_TO_MAP(string1[, string2, string3])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p980020744019">Splits <strong id="dli_08_15089__b151689192313">string1</strong> into key-value pairs using a separator and returns a map. <strong id="dli_08_15089__b05457817337">string2</strong> is the pair separator, and the default separator is a comma (,). <strong id="dli_08_15089__b678711305332">string3</strong> is the key-value separator, and the default separator is an equal sign (=).</p>
<p id="dli_08_15089__p14508135818395">Both separators are regular expressions, so special characters should be escaped beforehand, such as <strong id="dli_08_15089__b1624510340324">&lt;([{\^-=$!|]})?*+.&gt;</strong>.</p>
</td>
</tr>
<tr id="dli_08_15089__row772914441006"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1581914144409">SUBSTR(string[, integer1[, integer2]])</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p2023692174010">Returns a substring of a string starting from position <strong id="dli_08_15089__b172419343513">integer1</strong> with a length of <strong id="dli_08_15089__b6593128183517">integer2</strong> (default to the end).</p>
</td>
</tr>
<tr id="dli_08_15089__row9608134018219"><td class="cellrowborder" valign="top" width="28.610000000000003%" headers="mcps1.3.1.4.2.3.1.1 "><p id="dli_08_15089__p1761014299223">JSON_VAL(STRING json_string, STRING json_path)</p>
</td>
<td class="cellrowborder" valign="top" width="71.39%" headers="mcps1.3.1.4.2.3.1.2 "><p id="dli_08_15089__p16608124011214">Returns the value of the specified <strong id="dli_08_15089__b181930597044647">json_path</strong> from the <strong id="dli_08_15089__b38131622744647">json_string</strong>. For details about how to use the functions, see <a href="#dli_08_15089__section624613301257">JSON_VAL Function</a>.</p>
<div class="note" id="dli_08_15089__note1734061893313"><span class="notetitle"> NOTE: </span><div class="notebody"><p id="dli_08_15089__p129821342161516">The following rules are listed in descending order of priority.</p>
<ol id="dli_08_15089__ol17340154110164"><li id="dli_08_15089__li193401041191618">The two arguments <strong id="dli_08_15089__b22970223544647">json_string</strong> and <strong id="dli_08_15089__b202179959044647">json_path</strong> cannot be <strong id="dli_08_15089__b14964441844647">NULL</strong>.</li><li id="dli_08_15089__li722714431619">The value of <strong id="dli_08_15089__b35101945344647">json_string</strong> must be a valid JSON string. Otherwise, the function returns <strong id="dli_08_15089__b54959067044647">NULL</strong>.</li><li id="dli_08_15089__li1037194611610">If <strong id="dli_08_15089__b179330553344647">json_string</strong> is an empty string, the function returns an empty string.</li><li id="dli_08_15089__li837174816163">If <strong id="dli_08_15089__b141077722944647">json_path</strong> is an empty string or the path does not exist, the function returns <strong id="dli_08_15089__b546916344647">NULL</strong>.</li></ol>
</div></div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section" id="dli_08_15089__section624613301257"><a name="dli_08_15089__section624613301257"></a><a name="section624613301257"></a><h4 class="sectiontitle">JSON_VAL Function</h4><ul id="dli_08_15089__ul149287348263"><li id="dli_08_15089__li15929153422616">Syntax</li></ul>
<pre class="screen" id="dli_08_15089__screen112231124142612">STRING JSON_VAL(STRING json_string, STRING json_path)</pre>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="dli_08_15089__table343016642713" frame="border" border="1" rules="all"><caption><b>Table 2 </b>Parameters</caption><thead align="left"><tr id="dli_08_15089__row843114615273"><th align="left" class="cellrowborder" valign="top" width="20.782078207820785%" id="mcps1.3.2.4.2.4.1.1"><p id="dli_08_15089__p1643176142717">Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="26.632663266326627%" id="mcps1.3.2.4.2.4.1.2"><p id="dli_08_15089__p164312682715">Data Types</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="52.58525852585259%" id="mcps1.3.2.4.2.4.1.3"><p id="dli_08_15089__p143111662712">Description</p>
</th>
</tr>
</thead>
<tbody><tr id="dli_08_15089__row6431767278"><td class="cellrowborder" valign="top" width="20.782078207820785%" headers="mcps1.3.2.4.2.4.1.1 "><p id="dli_08_15089__p154315611276">json_string</p>
</td>
<td class="cellrowborder" valign="top" width="26.632663266326627%" headers="mcps1.3.2.4.2.4.1.2 "><p id="dli_08_15089__p17431166102716">STRING</p>
</td>
<td class="cellrowborder" valign="top" width="52.58525852585259%" headers="mcps1.3.2.4.2.4.1.3 "><p id="dli_08_15089__p194311463278">JSON object to be parsed</p>
</td>
</tr>
<tr id="dli_08_15089__row943118619270"><td class="cellrowborder" valign="top" width="20.782078207820785%" headers="mcps1.3.2.4.2.4.1.1 "><p id="dli_08_15089__p13431156192714">json_path</p>
</td>
<td class="cellrowborder" valign="top" width="26.632663266326627%" headers="mcps1.3.2.4.2.4.1.2 "><p id="dli_08_15089__p84318620278">STRING</p>
</td>
<td class="cellrowborder" valign="top" width="52.58525852585259%" headers="mcps1.3.2.4.2.4.1.3 "><p id="dli_08_15089__p14431166182710">Path expression for parsing the JSON string For the supported expressions, see <a href="#dli_08_15089__table147467547297">Table 3</a>.</p>
</td>
</tr>
</tbody>
</table>
</div>
<div class="tablenoborder"><a name="dli_08_15089__table147467547297"></a><a name="table147467547297"></a><table cellpadding="4" cellspacing="0" summary="" id="dli_08_15089__table147467547297" frame="border" border="1" rules="all"><caption><b>Table 3 </b>Expressions supported</caption><thead align="left"><tr id="dli_08_15089__row1874615549294"><th align="left" class="cellrowborder" valign="top" width="44.04%" id="mcps1.3.2.5.2.3.1.1"><p id="dli_08_15089__p7746155412914">Expression</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="55.96%" id="mcps1.3.2.5.2.3.1.2"><p id="dli_08_15089__p7746135413292">Description</p>
</th>
</tr>
</thead>
<tbody><tr id="dli_08_15089__row1574625472912"><td class="cellrowborder" valign="top" width="44.04%" headers="mcps1.3.2.5.2.3.1.1 "><p id="dli_08_15089__p1074655417299">$</p>
</td>
<td class="cellrowborder" valign="top" width="55.96%" headers="mcps1.3.2.5.2.3.1.2 "><p id="dli_08_15089__p107461354122914">Root node in the path</p>
</td>
</tr>
<tr id="dli_08_15089__row1746135452912"><td class="cellrowborder" valign="top" width="44.04%" headers="mcps1.3.2.5.2.3.1.1 "><p id="dli_08_15089__p5746165472913">[]</p>
</td>
<td class="cellrowborder" valign="top" width="55.96%" headers="mcps1.3.2.5.2.3.1.2 "><p id="dli_08_15089__p1374675492914">Access array elements</p>
</td>
</tr>
<tr id="dli_08_15089__row474645418294"><td class="cellrowborder" valign="top" width="44.04%" headers="mcps1.3.2.5.2.3.1.1 "><p id="dli_08_15089__p3746155492914">*</p>
</td>
<td class="cellrowborder" valign="top" width="55.96%" headers="mcps1.3.2.5.2.3.1.2 "><p id="dli_08_15089__p14746145482919">Array wildcard</p>
</td>
</tr>
<tr id="dli_08_15089__row480105719316"><td class="cellrowborder" valign="top" width="44.04%" headers="mcps1.3.2.5.2.3.1.1 "><p id="dli_08_15089__p1080165716311">.</p>
</td>
<td class="cellrowborder" valign="top" width="55.96%" headers="mcps1.3.2.5.2.3.1.2 "><p id="dli_08_15089__p980757183119">Access child elements</p>
</td>
</tr>
</tbody>
</table>
</div>
<ul id="dli_08_15089__ul141132115517"><li id="dli_08_15089__li911821255">Example<ol id="dli_08_15089__ol932913554614"><li id="dli_08_15089__li193291255367">Test input data.<div class="p" id="dli_08_15089__p1090101315812"><a name="dli_08_15089__li193291255367"></a><a name="li193291255367"></a>Test the data source kafka. The message content is as follows:<pre class="screen" id="dli_08_15089__screen193101913486">{"name":"James","age":24,"gender":"male","grade":{"math":95,"science":[80,85],"english":100}}</pre>
</div>
</li><li id="dli_08_15089__li1535210201774">Use JSON_VAL in SQL statements.<pre class="screen" id="dli_08_15089__screen32774318814">CREATE TABLE kafkaSource (
message string
) WITH (
'connector' = 'kafka',
'topic-pattern' = '&lt;yourSinkTopic&gt;',
'properties.bootstrap.servers' = '&lt;yourKafkaAddress1&gt;:&lt;yourKafkaPort&gt;,&lt;yourKafkaAddress2&gt;:&lt;yourKafkaPort&gt;',
'properties.group.id' = '&lt;yourGroupId&gt;',
'scan.startup.mode' = 'latest-offset',
'format' = 'csv',
'csv.field-delimiter' = '\u0001',
'csv.quote-character' = ''''
);
CREATE TABLE printSink (
message1 STRING,
message2 STRING,
message3 STRING,
message4 STRING,
message5 STRING,
message6 STRING
) WITH (
'connector' = 'print'
);
insert into printSink select
JSON_VAL(message,''),
JSON_VAL(message,'$.name'),
JSON_VAL(message,'$.grade.science'),
JSON_VAL(message,'$.grade.science[*]'),
JSON_VAL(message,'$.grade.science[1]'),
JSON_VAL(message,'$.grade.dddd')
from kafkaSource;</pre>
</li><li id="dli_08_15089__li8729420711">Check the output of the <strong id="dli_08_15089__b1906183723514">out</strong> file of the taskmanager.<pre class="screen" id="dli_08_15089__screen1742352518914">+I[null, James, [80,85], [80,85], 85, null]</pre>
</li></ol>
</li></ul>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dli_08_15085.html">Built-In Functions</a></div>
</div>
</div>
View Git Blame Copy Permalink