Updated on 2024-02-07 GMT+08:00

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

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

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.

    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

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

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

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

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

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

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

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

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

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.

    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.

    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) 

    Or

    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"