String Functions

Concatenates two strings.

VARCHAR VARCHAR a || VARCHAR b

• a: string.
• b: string.

• Test statement

  SELECT "hello" || "world";

• Test result

  "helloworld"

Returns the number of characters in a string.

INT CHAR_LENGTH(a)

• a: 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: 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: string
• var2: 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: string
• var2: 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: 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: 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 string contains only letters.

BOOLEAN IS_ALPHA(VARCHAR content)

• content: Enter a 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 string contains only digits.

BOOLEAN IS_DIGITS(VARCHAR content)

• content: Enter a 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 string is a numeric one.

BOOLEAN IS_NUMBER(VARCHAR content)

• content: Enter a 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 string is a valid URL.

BOOLEAN IS_URL(VARCHAR content)

• content: Enter a 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 string.

VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)

• content: Enter a 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 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: 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: string before concatenation.
• len: length of the concatenated string.
• pad: 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: 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: string.
• y: 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 string x does not exist in the queried string y, 0 is returned.

INTEGER POSITION(x IN y)

• x: string
• y: string.

• Test statement

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

• Test result

  Table 13 Test result

  result
  3

The 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 string.
• len: length of the new string.
• pad: 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: string.

• Test statement

  SELECT SHA1("abc");

• Test result

  "a9993e364706816aba3e25717850c26c9cd0d89d"

Returns the SHA256 value of the expr string.

STRING SHA256(STRING expr)

• expr: string.

• Test statement

  SELECT SHA256("abc");

• Test result

  "ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad"

Separates the value string as 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: string.
• delimiter: 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 string in reverse order.

VARCHAR SUBSTRING(STRING A FROM INT start) 

Or

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

• A: specified string.
• start: start position for truncating the 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: string.
• b: 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: string.

• Test statement

  SELECT UPPER("hello world");

• Test result

  "HELLO WORLD"