String Functions¶

The common string functions of DLI are as follows:

**Table 1** String operators¶
Operator	Returned Data Type	Description
\|\|	VARCHAR	Concatenates two strings.
CHAR_LENGTH	INT	Returns the number of characters in a string.
CHARACTER_LENGTH	INT	Returns the number of characters in a string.
CONCAT	VARCHAR	Concatenates two or more string values to form a new string. If the value of any parameter is NULL, skip this parameter.
CONCAT_WS	VARCHAR	Concatenates each parameter value and the separator specified by the first parameter separator to form a new string. The length and type of the new string depend on the input value.
HASH_CODE	INT	Returns the absolute value of HASH_CODE() of a string. In addition to string, int, bigint, float, and double are also supported.
INITCAP	VARCHAR	Returns a string whose first letter is in uppercase and the other letters in lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
IS_ALPHA	BOOLEAN	Checks whether a string contains only letters.
IS_DIGITS	BOOLEAN	Checks whether a string contains only digits.
IS_NUMBER	BOOLEAN	Checks whether a string is numeric.
IS_URL	BOOLEAN	Checks whether a string is a valid URL.
JSON_VALUE	VARCHAR	Obtains the value of a specified path in a JSON string.
KEY_VALUE	VARCHAR	Obtains the value of a key in a key-value pair string.
LOWER	VARCHAR	Returns a string of lowercase characters.
LPAD	VARCHAR	Concatenates the pad string to the left of the str string until the length of the new string reaches the specified length len.
MD5	VARCHAR	Returns the MD5 value of a string. If the parameter is an empty string (that is, the parameter is "), an empty string is returned.
OVERLAY	VARCHAR	Replaces the substring of x with y. Replace length+1 characters starting from start_position.
POSITION	INT	Returns the position of the first occurrence of the target string x in the queried string y. If the target string x does not exist in the queried string y, 0 is returned.
REPLACE	VARCHAR	Replaces all str2 in the str1 string with str3. str1: original character. str2: target character. str3: replacement character.
RPAD	VARCHAR	Concatenates the pad string to the right of the str string until the length of the new string reaches the specified length len.
SHA1	STRING	Returns the SHA1 value of the expr string.
SHA256	STRING	Returns the SHA256 value of the expr string.
STRING_TO_ARRAY	ARRAY[STRING]	Separates the value string as string arrays by using the delimiter.
SUBSTRING	VARCHAR	Returns the substring starting from a fixed position of A. The start position starts from 1.
TRIM	STRING	Removes A at the start position, or end position, or both the start and end positions from B. By default, string expressions A at both the start and end positions are removed.
UPPER	VARCHAR	Returns a string converted to uppercase characters.

||¶

Function
Concatenates two strings.
Syntax
```
VARCHAR VARCHAR a || VARCHAR b
```
Parameters
- a: string.
- b: string.

Example

Test statement
```
SELECT "hello" || "world";
```
Test result
```
"helloworld"
```

CHAR_LENGTH¶

Function
Returns the number of characters in a string.
Syntax
```
INT CHAR_LENGTH(a)
```
Parameters
- a: string.
Example
- Test statement
```
SELECT  CHAR_LENGTH(var1) as aa FROM T1;
```
- Test data and result
  Table 2 Test data and result¶
  Test Data (var1)
  Test Result (aa)
  abcde123
  8

**Table 2** Test data and result¶
Test Data (var1)	Test Result (aa)
abcde123	8

CHARACTER_LENGTH¶

Function
Returns the number of characters in a string.
Syntax
```
INT CHARACTER_LENGTH(a)
```
Parameters
- a: string.
Example
- Test statement
```
SELECT  CHARACTER_LENGTH(var1) as aa FROM T1;
```
- Test data and result
  Table 3 Test data and result¶
  Test Data (var1)
  Test Result (aa)
  abcde123
  8

**Table 3** Test data and result¶
Test Data (var1)	Test Result (aa)
abcde123	8

CONCAT¶

Function
Concatenates two or more string values to form a new string. If the value of any parameter is NULL, skip this parameter.

Syntax

VARCHAR CONCAT(VARCHAR var1, VARCHAR var2, ...)

Parameters
- var1: string
- var2: string

Example

Test statement

SELECT CONCAT("abc", "def", "ghi", "jkl");

Test result
```
"abcdefghijkl"
```

CONCAT_WS¶

Function
Concatenates each parameter value and the separator specified by the first parameter separator to form a new string. The length and type of the new string depend on the input value.
Note
If the value of separator is null, separator is combined with an empty string. If other parameters are set to null, the parameters whose values are null are skipped during combination.

Syntax

VARCHAR CONCAT_WS(VARCHAR separator, VARCHAR var1, VARCHAR var2, ...)

Parameters
- separator: separator.
- var1: string
- var2: string

Example

Test statement

SELECT CONCAT_WS("-", "abc", "def", "ghi", "jkl");

Test result
```
"abc-def-ghi-jkl"
```

HASH_CODE¶

Function
Returns the absolute value of HASH_CODE() of a string. In addition to string, int, bigint, float, and double are also supported.
Syntax
```
INT HASH_CODE(VARCHAR str)
```
Parameters
- str: string.
Example
- Test statement
```
SELECT HASH_CODE("abc");
```
- Test result
```
96354
```

INITCAP¶

Function
Return the string whose first letter is in uppercase and the other letters in lowercase. Strings are sequences of alphanumeric characters separated by non-alphanumeric characters.
Syntax
```
VARCHAR INITCAP(a)
```
Parameters
- a: string.
Example
- Test statement
```
SELECT INITCAP(var1)as aa FROM T1;
```
- Test data and result
  Table 4 Test data and result¶
  Test Data (var1)
  Test Result (aa)
  aBCde
  Abcde

**Table 4** Test data and result¶
Test Data (var1)	Test Result (aa)
aBCde	Abcde

IS_ALPHA¶

Function
Checks whether a string contains only letters.
Syntax
```
BOOLEAN IS_ALPHA(VARCHAR content)
```
Parameters
- content: Enter a string.
Example
- Test statement
```
SELECT IS_ALPHA(content)  AS case_result FROM T1;
```
- Test data and results
  Table 5 Test data and results¶
  Test Data (content)
  Test Result (case_result)
  Abc
  true
  abc1#$
  false
  null
  false
  Empty string
  false

**Table 5** Test data and results¶
Test Data (content)	Test Result (case_result)
Abc	true
abc1#$	false
null	false
Empty string	false

IS_DIGITS¶

Function
Checks whether a string contains only digits.
Syntax
```
BOOLEAN IS_DIGITS(VARCHAR content)
```
Parameters
- content: Enter a string.
Example
- Test statement
```
SELECT IS_DIGITS(content) AS case_result FROM T1;
```
- Test data and results
  Table 6 Test data and results¶
  Test Data (content)
  Test Result (case_result)
  78
  true
  78.0
  false
  78a
  false
  null
  false
  Empty string
  false

**Table 6** Test data and results¶
Test Data (content)	Test Result (case_result)
78	true
78.0	false
78a	false
null	false
Empty string	false

IS_NUMBER¶

Function
This function is used to check whether a string is a numeric one.
Syntax
```
BOOLEAN IS_NUMBER(VARCHAR content)
```
Parameters
- content: Enter a string.
Example
- Test statement
```
SELECT IS_NUMBER(content) AS case_result FROM T1;
```
- Test data and results
  Table 7 Test data and results¶
  Test Data (content)
  Test Result (case_result)
  78
  true
  78.0
  true
  78a
  false
  null
  false
  Empty string
  false

**Table 7** Test data and results¶
Test Data (content)	Test Result (case_result)
78	true
78.0	true
78a	false
null	false
Empty string	false

IS_URL¶

Function
This function is used to check whether a string is a valid URL.
Syntax
```
BOOLEAN IS_URL(VARCHAR content)
```
Parameters
- content: Enter a string.
Example
- Test statement
```
SELECT IS_URL(content) AS case_result FROM T1;
```
- Test data and results
  Table 8 Test data and results¶
  Test Data (content)
  Test Result (case_result)
  https://www.testweb.com
  true
  https://www.testweb.com:443
  true
  www.testweb.com:443
  false
  null
  false
  Empty string
  false

**Table 8** Test data and results¶
Test Data (content)	Test Result (case_result)
https://www.testweb.com	true
https://www.testweb.com:443	true
www.testweb.com:443	false
null	false
Empty string	false

JSON_VALUE¶

Function
Obtains the value of a specified path in a JSON string.

Syntax

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)

Parameters
- content: Enter a string.
- path: path to be obtained.

Example

Test statement

SELECT JSON_VALUE(content, path) AS case_result FROM T1;

Test data and results

**Table 9** Test data and results¶
Test Data (content and path)		Test Result (case_result)
{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}	$	{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}
{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}	$.a1	v1
{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}	$.a4	{"a41":"v41","a42": ["v1","v2"]}
{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}	$.a4.a42	["v1","v2"]
{ "a1":"v1","a2":7,"a3":8.0,"a4": {"a41":"v41","a42": ["v1","v2"]}}	$.a4.a42[0]	v1

KEY_VALUE¶

Function
This function is used to obtain the value of a key in a key-value pair string.

Syntax

VARCHAR KEY_VALUE(VARCHAR content, VARCHAR split1, VARCHAR split2, VARCHAR key_name)

Parameters
- content: Enter a string.
- split1: separator of multiple key-value pairs.
- split2: separator between the key and value.
- key_name: name of the key to be obtained.
Example
- Test statement
```
SELECT KEY_VALUE(content, split1, split2, key_name)  AS case_result FROM T1;
```
- Test data and results
  Table 10 Test data and results¶
  Test Data (content, split1, split2, and key_name)
  Test Result (case_result)
  k1=v1;k2=v2
  ;
  =
  k1
  v1
  null
  ;
  =
  k1
  null
  k1=v1;k2=v2
  null
  =
  k1
  null

**Table 10** Test data and results¶
Test Data (content, split1, split2, and key_name)				Test Result (case_result)
k1=v1;k2=v2	;	=	k1	v1
null	;	=	k1	null
k1=v1;k2=v2	null	=	k1	null

LOWER¶

Function
Returns a string of lowercase characters.
Syntax
```
VARCHAR LOWER(A)
```
Parameters
- A: string.
Example
- Test statement
```
SELECT LOWER(var1) AS aa FROM T1;
```
- Test data and result
  Table 11 Test data and result¶
  Test Data (var1)
  Test Result (aa)
  ABc
  abc

**Table 11** Test data and result¶
Test Data (var1)	Test Result (aa)
ABc	abc

LPAD¶

Function
Concatenates the pad string to the left of the str string until the length of the new string reaches the specified length len.

Syntax

VARCHAR LPAD(VARCHAR str, INT len, VARCHAR pad)

Parameters
- str: string before concatenation.
- len: length of the concatenated string.
- pad: string to be concatenated.
Note
- If any parameter is null, null is returned.
- If the value of len is a negative number, value null is returned.
- If the value of len is less than the length of str, the first chunk of str characters in len length is returned.

Example

Test statement

SELECT
  LPAD("adc", 2, "hello"),
  LPAD("adc", -1, "hello"),
  LPAD("adc", 10, "hello");

Test result
```
"ad",,"helloheadc"
```

MD5¶

Function
Returns the MD5 value of a string. If the parameter is an empty string (that is, the parameter is "), an empty string is returned.
Syntax
```
VARCHAR MD5(VARCHAR str)
```
Parameters
- str: string

Example

Test statement
```
SELECT MD5("abc");
```
Test result
```
"900150983cd24fb0d6963f7d28e17f72"
```

OVERLAY¶

Function
Replaces the substring of x with y. Replaces length+1 characters starting from start_position.

Syntax

VARCHAR OVERLAY ( (VARCHAR x PLACING VARCHAR y FROM INT start_position [ FOR INT length ]) )

Parameters
- x: string.
- y: string.
- start_position: start position.
- length (optional): indicates the character length.

Example

Test statement

OVERLAY('abcdefg' PLACING 'xyz' FROM 2 FOR 2) AS result FROM T1;

Test result
Table 12 Test result¶
result
axyzdefg

**Table 12** Test result¶
result
axyzdefg

POSITION¶

Function
Returns the position of the first occurrence of the target string x in the queried string y. If the target string x does not exist in the queried string y, 0 is returned.
Syntax
```
INTEGER POSITION(x IN y)
```
Parameters
- x: string
- y: string.
Example
- Test statement
```
POSITION('in' IN 'chin') AS result FROM T1;
```
- Test result
  Table 13 Test result¶
  result
  3

**Table 13** Test result¶
result
3

REPLACE¶

Function
The string replacement function is used to replace all str2 in the str1 string with str3.

Syntax

VARCHAR REPLACE(VARCHAR str1, VARCHAR str2, VARCHAR str3)

Parameters
- str1: original character.
- str2: target character.
- str3: replacement character.

Example

Test statement

SELECT
  replace(
    "hello world hello world hello world",
    "world",
    "hello"
  );

Test result
```
"hello hello hello hello hello hello"
```

RPAD¶

Function
Concatenates the pad string to the right of the str string until the length of the new string reaches the specified length len.
- If any parameter is null, null is returned.
- If the value of len is a negative number, value null is returned.
- The value of pad is an empty string. If the value of len is less than the length of str, the string whose length is the same as the length of str is returned.

Syntax

VARCHAR RPAD(VARCHAR str, INT len, VARCHAR pad)

Parameters
- str: start string.
- len: length of the new string.
- pad: string that needs to be added repeatedly.

Example

Test statement

SELECT
  RPAD("adc", 2, "hello"),
  RPAD("adc", -1, "hello"),
  RPAD("adc", 10, "hello");

Test result
```
"ad",,"adchellohe"
```

SHA1¶

Function
Returns the SHA1 value of the expr string.
Syntax
```
STRING SHA1(STRING expr)
```
Parameters
- expr: string.

Example

Test statement
```
SELECT SHA1("abc");
```

Test result

"a9993e364706816aba3e25717850c26c9cd0d89d"

SHA256¶

Function
Returns the SHA256 value of the expr string.
Syntax
```
STRING SHA256(STRING expr)
```
Parameters
- expr: string.

Example

Test statement
```
SELECT SHA256("abc");
```

Test result

"ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad"

STRING_TO_ARRAY¶

Function
Separates the value string as string arrays by using the delimiter.
Note
delimiter uses the Java regular expression. If special characters are used, they need to be escaped.

Syntax

ARRAY[String] STRING_TO_ARRAY(STRING value, VARCHAR delimiter)

Parameters
- value: string.
- delimiter: delimiter.

Example

Test statement

SELECT
  string_to_array("127.0.0.1", "\\."),
  string_to_array("red-black-white-blue", "-");

Test result
```
[127,0,0,1],[red,black,white,blue]
```

SUBSTRING¶

Function
Returns the substring that starts from a fixed position of A. The start position starts from 1.
- If len is not specified, the substring from the start position to the end of the string is truncated.
- If len is specified, the substring starting from the position specified by start is truncated. The length is specified by len.
Note
The value of start starts from 1. If the value is 0, it is regarded as 1. If the value of start is a negative number, the position is calculated from the end of the string in reverse order.

Syntax

VARCHAR SUBSTRING(STRING A FROM INT start)

VARCHAR SUBSTRING(STRING A FROM INT start FOR INT len)

Parameters
- A: specified string.
- start: start position for truncating the string A.
- len: intercepted length.

Example

Test statement 1
```
SELECT SUBSTRING("123456" FROM 2);
```
Test result 1
```
"23456"
```

Test statement 2

SELECT SUBSTRING("123456" FROM 2 FOR 4);

Test result 2
```
"2345"
```

TRIM¶

Function
Remove A at the start position, or end position, or both the start and end positions from B. By default, string expressions A at both the start and end positions are removed.

Syntax

STRING TRIM( { BOTH | LEADING | TRAILING } STRING a FROM STRING b)

Parameters
- a: string.
- b: string.

Example

Test statement

SELECT TRIM(BOTH " " FROM "  hello world  ");

Test result
```
"hello world"
```

UPPER¶

Function
Returns a string converted to an uppercase character.
Syntax
```
VARCHAR UPPER(A)
```
Parameters
- A: string.

Example

Test statement
```
SELECT UPPER("hello world");
```
Test result
```
"HELLO WORLD"
```

last updated: 2024-11-20 15:34 UTC - commit: c856450509a511f9057fb9577287974ed6fce406