String Functions

Concatenates two character strings.

VARCHAR VARCHAR a || VARCHAR b

• a: character string.
• b: character string.

• Test statement

  SELECT "hello" || "world";

• Test result

  "helloworld"

Returns the number of characters in a string.

INT CHAR_LENGTH(a)

• a: character string.

• 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

Returns the number of characters in a string.

INT CHARACTER_LENGTH(a)

• a: character string.

• 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

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

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

• var1: character string
• var2: character string

• Test statement

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

• Test result

  "abcdefghijkl"

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.

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.

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

• separator: separator.
• var1: character string
• var2: character string

• Test statement

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

• Test result

  "abc-def-ghi-jkl"

Returns the absolute value of HASH_CODE() of a string. In addition to string, int, bigint, float, and double are also supported.

INT HASH_CODE(VARCHAR str)

• str: character string.

• Test statement

  SELECT HASH_CODE("abc");

• Test result

  96354

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.

VARCHAR INITCAP(a)

• a: character string.

• 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

Checks whether a character string contains only letters.

BOOLEAN IS_ALPHA(VARCHAR content)

• content: Enter a character string.

• 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

Checks whether a character string contains only digits.

BOOLEAN IS_DIGITS(VARCHAR content)

• content: Enter a character string.

• 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

This function is used to check whether a character string is a numeric string.

BOOLEAN IS_NUMBER(VARCHAR content)

• content: Enter a character string.

• 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

This function is used to check whether a character string is a valid URL.

BOOLEAN IS_URL(VARCHAR content)

• content: Enter a character string.

• 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

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

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)

• content: Enter a character string.
• path: path to be obtained.

• 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

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

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

• content: Enter a character string.
• split1: separator of multiple key-value pairs.
• split2: separator between the key and value.
• key_name: name of the key to be obtained.

• 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

Returns a string of lowercase characters.

VARCHAR LOWER(A)

• A: character string.

• 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

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

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

• str: character string before concatenation.
• len: length of the concatenated character string.
• pad: character string to be concatenated.

• 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.

• Test statement

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

• Test result

  "ad",,"helloheadc"

Returns the MD5 value of a string. If the parameter is an empty string (that is, the parameter is "), an empty string is returned.

VARCHAR MD5(VARCHAR str)

• str: character string

• Test statement

  SELECT MD5("abc");

• Test result

  "900150983cd24fb0d6963f7d28e17f72"

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

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

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

• Test statement

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

• Test result

  Table 12 Test result

  result
  axyzdefg

Returns the position of the first occurrence of the target string x in the queried string y. If the target character string x does not exist in the queried character string y, 0 is returned.

INTEGER POSITION(x IN y)

• x: character string
• y: character string.

• Test statement

  POSITION('in' IN 'chin') AS result FROM T1;

• Test result

  Table 13 Test result

  result
  3

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

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

• str1: original character.
• str2: target character.
• str3: replacement character.

• Test statement

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

• Test result

  "hello hello hello hello hello hello"

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.

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

• str: start character string.
• len: indicates the length of the new character string.
• pad: character string that needs to be added repeatedly.

• Test statement

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

• Test result

  "ad",,"adchellohe"

Returns the SHA1 value of the expr string.

STRING SHA1(STRING expr)

• expr: character string.

• Test statement

  SELECT SHA1("abc");

• Test result

  "a9993e364706816aba3e25717850c26c9cd0d89d"

Returns the SHA256 value of the expr string.

STRING SHA256(STRING expr)

• expr: character string.

• Test statement

  SELECT SHA256("abc");

• Test result

  "ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad"

Separates the value string as character string arrays by using the delimiter.

delimiter uses the Java regular expression. If special characters are used, they need to be escaped.

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

• value: character string.
• delimiter: specifies the delimiter.

• 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]

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.

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 character string in reverse order.

VARCHAR SUBSTRING(STRING A FROM INT start) 

Or

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

• A: specified character string.
• start: start position for truncating the character string A.
• len: intercepted length.

• 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"

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.

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

• a: character string.
• b: character string.

• Test statement

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

• Test result

  "hello world"

Returns a string converted to an uppercase character.

VARCHAR UPPER(A)

• A: character string.

• Test statement

  SELECT UPPER("hello world");

• Test result

  "HELLO WORLD"