This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

String Functions

1: ASCII
2: BIN
3: BIT_LENGTH
4: CHAR
5: CHAR_LENGTH
6: CHARACTER_LENGTH
7: CONCAT
8: CONCAT_WS
9: FROM_BASE64
10: FROM_HEX
11: HEX
12: INSERT
13: INSTR
14: JARO_WINKLER
15: LCASE
16: LEFT
17: LENGTH
18: LENGTH_UTF8
19: LIKE
20: LOCATE
21: LOWER
22: LPAD
23: LTRIM
24: MID
25: NOT LIKE
26: NOT REGEXP
27: NOT RLIKE
28: OCT
29: OCTET_LENGTH
30: ORD
31: POSITION
32: QUOTE
33: REGEXP
34: REGEXP_INSTR
35: REGEXP_LIKE
36: REGEXP_REPLACE
37: REGEXP_SUBSTR
38: REPEAT
39: REPLACE
40: REVERSE
41: RIGHT
42: RLIKE
43: RPAD
44: RTRIM
45: SOUNDEX
46: SOUNDS LIKE
47: SPACE
48: SPLIT
49: SPLIT_PART
50: STRCMP
51: SUBSTR
52: SUBSTRING
53: TO_BASE64
54: TRANSLATE
55: TRIM
56: TRIM_BOTH
57: TRIM_LEADING
58: TRIM_TRAILING
59: UCASE
60: UNHEX
61: UPPER

This section provides reference information for the string-related functions in PlaidCloud Lakehouse.

String Manipulation:

String Information:

Case Conversion:

Regular Expressions:

Encoding and Decoding:

Miscellaneous:

1 - ASCII

Returns the numeric value of the leftmost character of the string str.

Analyze Syntax

func.ascii(<expr>)

Analyze Examples

func.ascii('2')
+-----------------+
| func.ascii('2') |
+-----------------+
|              50 |
+-----------------+

SQL Syntax

ASCII(<expr>)

Arguments

Arguments	Description
`<expr>`	The string.

Return Type

TINYINT

SQL Examples

SELECT ASCII('2');
+------------+
| ASCII('2') |
+------------+
|         50 |
+------------+

2 - BIN

Returns a string representation of the binary value of N.

Analyze Syntax

func.bin(<expr>)

Analyze Examples

func.bin(12)
+--------------+
| func.bin(12) |
+--------------+
| 1100         |
+--------------+

SQL Syntax

BIN(<expr>)

Arguments

Arguments	Description
`<expr>`	The number.

Return Type

VARCHAR

SQL Examples

SELECT BIN(12);
+---------+
| BIN(12) |
+---------+
| 1100    |
+---------+

3 - BIT_LENGTH

Return the length of a string in bits.

Analyze Syntax

func.bit_length(<expr>)

Analyze Examples

func.bit_length('Word')
+-------------------------+
| func.bit_length('Word') |
+-------------------------+
|                      32 |
+-------------------------+

SQL Syntax

BIT_LENGTH(<expr>)

Arguments

Arguments	Description
`<expr>`	The string.

Return Type

BIGINT

SQL Examples

SELECT BIT_LENGTH('Word');
+----------------------------+
| SELECT BIT_LENGTH('Word'); |
+----------------------------+
| 32                         |
+----------------------------+

4 - CHAR

Return the character for each integer passed.

Analyze Syntax

func.char(N,...)

Analyze Examples

func.char(77,121,83,81,76)
+-----------------------------+
| func.char(77,121,83,81,76) |
+-----------------------------+
| 4D7953514C                  |
+-----------------------------+

SQL Syntax

CHAR(N, ...)

Arguments

Arguments	Description
N	Numeric Column

Return Type

BINARY

SQL Examples

This example shows both the binary value returned as well as the string representation.

SELECT CHAR(77,121,83,81,76) as a, a::String;
┌────────────────────────┐
│      a     │ a::string │
│   Binary   │   String  │
├────────────┼───────────┤
│ 4D7953514C │ MySQL     │
└────────────────────────┘

5 - CHAR_LENGTH

Alias for LENGTH.

6 - CHARACTER_LENGTH

Alias for LENGTH.

7 - CONCAT

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

Analyze Syntax

func.concat(<expr1>, ...)

Analyze Examples

func.concat('data', 'bend')
+-----------------------------+
| func.concat('data', 'bend') |
+-----------------------------+
| databend                    |
+-----------------------------+

SQL Syntax

CONCAT(<expr1>, ...)

Arguments

Arguments	Description
`<expr1>`	string

Return Type

A VARCHAR data type value Or NULL data type.

SQL Examples

SELECT CONCAT('data', 'bend');
+------------------------+
| concat('data', 'bend') |
+------------------------+
| databend               |
+------------------------+

SELECT CONCAT('data', NULL, 'bend');
+------------------------------+
| CONCAT('data', NULL, 'bend') |
+------------------------------+
|                         NULL |
+------------------------------+

SELECT CONCAT('14.3');
+----------------+
| concat('14.3') |
+----------------+
| 14.3           |
+----------------+

8 - CONCAT_WS

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Analyze Syntax

func.concat_ws(<separator>, <expr1>, ...)

Analyze Examples

func.concat_ws(',', 'data', 'fuse', 'labs', '2021')
+-----------------------------------------------------+
| func.concat_ws(',', 'data', 'fuse', 'labs', '2021') |
+-----------------------------------------------------+
| data,fuse,labs,2021                                 |
+-----------------------------------------------------+

SQL Syntax

CONCAT_WS(<separator>, <expr1>, ...)

Arguments

Arguments	Description
`<separator>`	string column
`<expr1>`	value column

Return Type

A VARCHAR data type value Or NULL data type.

SQL Examples

SELECT CONCAT_WS(',', 'data', 'fuse', 'labs', '2021');
+------------------------------------------------+
| CONCAT_WS(',', 'data', 'fuse', 'labs', '2021') |
+------------------------------------------------+
| data,fuse,labs,2021                            |
+------------------------------------------------+

SELECT CONCAT_WS(',', 'data', NULL, 'bend');
+--------------------------------------+
| CONCAT_WS(',', 'data', NULL, 'bend') |
+--------------------------------------+
| data,bend                            |
+--------------------------------------+


SELECT CONCAT_WS(',', 'data', NULL, NULL, 'bend');
+--------------------------------------------+
| CONCAT_WS(',', 'data', NULL, NULL, 'bend') |
+--------------------------------------------+
| data,bend                                  |
+--------------------------------------------+


SELECT CONCAT_WS(NULL, 'data', 'fuse', 'labs');
+-----------------------------------------+
| CONCAT_WS(NULL, 'data', 'fuse', 'labs') |
+-----------------------------------------+
|                                    NULL |
+-----------------------------------------+

SELECT CONCAT_WS(',', NULL);
+----------------------+
| CONCAT_WS(',', NULL) |
+----------------------+
|                      |
+----------------------+

9 - FROM_BASE64

Takes a string encoded with the base-64 encoded rules nd returns the decoded result as a binary. The result is NULL if the argument is NULL or not a valid base-64 string.

Analyze Syntax

func.from_base64(<expr>)

Analyze Examples

func.from_base64('YWJj')
+--------------------------+
| func.from_base64('YWJj') |
+--------------------------+
| abc                      |
+--------------------------+

SQL Syntax

FROM_BASE64(<expr>)

Arguments

Arguments	Description
`<expr>`	The string value.

Return Type

BINARY

SQL Examples

SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc')) as b, b::String;
┌───────────────────────────────────────┐
│ to_base64('abc') │    b   │ b::string │
│      String      │ Binary │   String  │
├──────────────────┼────────┼───────────┤
│ YWJj             │ 616263 │ abc       │
└───────────────────────────────────────┘

10 - FROM_HEX

Alias for UNHEX.

11 - HEX

Alias for TO_HEX.

12 - INSERT

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

Analyze Syntax

func.insert(<str>, <pos>, <len>, <newstr>)

Analyze Examples

func.insert('Quadratic', 3, 4, 'What')
+----------------------------------------+
| func.insert('Quadratic', 3, 4, 'What') |
+----------------------------------------+
| QuWhattic                              |
+----------------------------------------+

SQL Syntax

INSERT(<str>, <pos>, <len>, <newstr>)

Arguments

Arguments	Description
`<str>`	The string.
`<pos>`	The position.
`<len>`	The length.
`<newstr>`	The new string.

Return Type

VARCHAR

SQL Examples

SELECT INSERT('Quadratic', 3, 4, 'What');
+-----------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+-----------------------------------+
| QuWhattic                         |
+-----------------------------------+

SELECT INSERT('Quadratic', -1, 4, 'What');
+---------------------------------------+
| INSERT('Quadratic', (- 1), 4, 'What') |
+---------------------------------------+
| Quadratic                             |
+---------------------------------------+

SELECT INSERT('Quadratic', 3, 100, 'What');
+-------------------------------------+
| INSERT('Quadratic', 3, 100, 'What') |
+-------------------------------------+
| QuWhat                              |
+-------------------------------------+

+--------------------------------------------+--------+
| INSERT('123456789', number, number, 'aaa') | number |
+--------------------------------------------+--------+
| 123456789                                  |      0 |
| aaa23456789                                |      1 |
| 1aaa456789                                 |      2 |
| 12aaa6789                                  |      3 |
| 123aaa89                                   |      4 |
| 1234aaa                                    |      5 |
| 12345aaa                                   |      6 |
| 123456aaa                                  |      7 |
| 1234567aaa                                 |      8 |
| 12345678aaa                                |      9 |
| 123456789                                  |     10 |
| 123456789                                  |     11 |
| 123456789                                  |     12 |
+--------------------------------------------+--------+

13 - INSTR

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

Analyze Syntax

func.instr(<str>, <substr>)

Analyze Examples

func.instr('foobarbar', 'bar')
+--------------------------------+
| func.instr('foobarbar', 'bar') |
+--------------------------------+
|                              4 |
+--------------------------------+

SQL Syntax

INSTR(<str>, <substr>)

Arguments

Arguments	Description
`<str>`	The string.
`<substr>`	The substring.

Return Type

BIGINT

SQL Examples

SELECT INSTR('foobarbar', 'bar');
+---------------------------+
| INSTR('foobarbar', 'bar') |
+---------------------------+
|                         4 |
+---------------------------+

SELECT INSTR('xbar', 'foobar');
+-------------------------+
| INSTR('xbar', 'foobar') |
+-------------------------+
|                       0 |
+-------------------------+

14 - JARO_WINKLER

Calculates the Jaro-Winkler distance between two strings. It is commonly used for measuring the similarity between strings, with values ranging from 0.0 (completely dissimilar) to 1.0 (identical strings).

Analyze Syntax

func.jaro_winkler(<string1>, <string2>)

Analyze Examples

func.jaro_winkler('databend', 'Databend')
+-------------------------------------------+
| func.jaro_winkler('databend', 'Databend') |
+-------------------------------------------+
|                        0.9166666666666666 |
+-------------------------------------------+

SQL Syntax

JARO_WINKLER(<string1>, <string2>)

SQL Examples

The JARO_WINKLER function returns a FLOAT64 value representing the similarity between the two input strings. The return value follows these rules:

Similarity Range: The result ranges from 0.0 (completely dissimilar) to 1.0 (identical).

SELECT JARO_WINKLER('databend', 'Databend') AS similarity;

┌────────────────────┐
│     similarity     │
├────────────────────┤
│ 0.9166666666666666 │
└────────────────────┘

SELECT JARO_WINKLER('databend', 'database') AS similarity;

┌────────────┐
│ similarity │
├────────────┤
│        0.9 │
└────────────┘

NULL Handling: If either string1 or string2 is NULL, the result is NULL.

SELECT JARO_WINKLER('databend', NULL) AS similarity;

┌────────────┐
│ similarity │
├────────────┤
│ NULL       │
└────────────┘

Empty Strings:

Comparing two empty strings returns 1.0.

SELECT JARO_WINKLER('', '') AS similarity;

┌────────────┐
│ similarity │
├────────────┤
│          1 │
└────────────┘

Comparing an empty string with a non-empty string returns 0.0.

SELECT JARO_WINKLER('databend', '') AS similarity;

┌────────────┐
│ similarity │
├────────────┤
│          0 │
└────────────┘

15 - LCASE

Alias for LOWER.

16 - LEFT

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

Analyze Syntax

func.left(<str>, <len>)

Analyze Examples

func.left('foobarbar', 5)
+---------------------------+
| func.left('foobarbar', 5) |
+---------------------------+
| fooba                     |
+---------------------------+

SQL Syntax

LEFT(<str>, <len>);

Arguments

Arguments	Description
`<str>`	The main string from where the character to be extracted
`<len>`	The count of characters

Return Type

VARCHAR

SQL Examples

SELECT LEFT('foobarbar', 5);
+----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba                |
+----------------------+

17 - LENGTH

Returns the length of a given input string or binary value. In the case of strings, the length represents the count of characters, with each UTF-8 character considered as a single character. For binary data, the length corresponds to the number of bytes.

Analyze Syntax

func.length(<expr>)

Analyze Examples

func.length('Hello')
+----------------------+
| func.length('Hello') |
+----------------------+
|                    5 |
+----------------------+

SQL Syntax

LENGTH(<expr>)

Aliases

Return Type

BIGINT

SQL Examples

SELECT LENGTH('Hello'), LENGTH_UTF8('Hello'), CHAR_LENGTH('Hello'), CHARACTER_LENGTH('Hello');

┌───────────────────────────────────────────────────────────────────────────────────────────┐
│ length('hello') │ length_utf8('hello') │ char_length('hello') │ character_length('hello') │
├─────────────────┼──────────────────────┼──────────────────────┼───────────────────────────┤
│               5 │                    5 │                    5 │                         5 │
└───────────────────────────────────────────────────────────────────────────────────────────┘

18 - LENGTH_UTF8

Alias for LENGTH.

19 - LIKE

Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

Analyze Syntax

<column>.like('plaid%')

Analyze Examples

my_clothes.like('plaid%)
+-----------------+
| my_clothes      |
+-----------------+
| plaid pants     |
| plaid hat       |
| plaid shirt     |
+-----------------+

SQL Syntax

<expr> LIKE <pattern>

SQL Examples

SELECT name, category FROM system.functions WHERE name like 'tou%' ORDER BY name;
+----------+------------+
| name     | category   |
+----------+------------+
| touint16 | conversion |
| touint32 | conversion |
| touint64 | conversion |
| touint8  | conversion |
+----------+------------+

20 - LOCATE

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.

Analyze Syntax

func.locate(<substr>, <str>, <pos>)

Analyze Examples

func.locate('bar', 'foobarbar')
+------------------------------------+
| func.locate('bar', 'foobarbar') |
+------------------------------------+
|                                  5 |
+------------------------------------+

func.locate('bar', 'foobarbar', 5)
+------------------------------------+
| func.locate('bar', 'foobarbar', 5) |
+------------------------------------+
|                                  7 |
+------------------------------------+

SQL Syntax

LOCATE(<substr>, <str>)
LOCATE(<substr>, <str>, <pos>)

Arguments

Arguments	Description
`<substr>`	The substring.
`<str>`	The string.
`<pos>`	The position.

Return Type

BIGINT

SQL Examples

SELECT LOCATE('bar', 'foobarbar')
+----------------------------+
| LOCATE('bar', 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+

SELECT LOCATE('xbar', 'foobar')
+--------------------------+
| LOCATE('xbar', 'foobar') |
+--------------------------+
|                        0 |
+--------------------------+

SELECT LOCATE('bar', 'foobarbar', 5)
+-------------------------------+
| LOCATE('bar', 'foobarbar', 5) |
+-------------------------------+
|                             7 |
+-------------------------------+

21 - LOWER

Returns a string with all characters changed to lowercase.

Analyze Syntax

func.lower(<str>)

Analyze Examples

func.lower('Hello, PlaidCloud!')
+----------------------------------+
| func.lower('Hello, PlaidCloud!') |
+----------------------------------+
| hello, plaidcloud!               |
+----------------------------------+

SQL Syntax

LOWER(<str>)

Aliases

LCASE

Return Type

VARCHAR

SQL Examples

SELECT LOWER('Hello, Databend!'), LCASE('Hello, Databend!');

┌───────────────────────────────────────────────────────┐
│ lower('hello, databend!') │ lcase('hello, databend!') │
├───────────────────────────┼───────────────────────────┤
│ hello, databend!          │ hello, databend!          │
└───────────────────────────────────────────────────────┘

22 - LPAD

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

Analyze Syntax

func.lpad(<str>, <len>, <padstr>)

Analyze Examples

func.lpad('hi',4,'??')
+------------------------+
| func.lpad('hi',4,'??') |
+------------------------+
| ??hi                   |
+------------------------+

func.lpad('hi',1,'??')
+------------------------+
| func.lpad('hi',1,'??') |
+------------------------+
| h                      |
+------------------------+

SQL Syntax

LPAD(<str>, <len>, <padstr>)

Arguments

Arguments	Description
`<str>`	The string.
`<len>`	The length.
`<padstr>`	The pad string.

Return Type

VARCHAR

SQL Examples

SELECT LPAD('hi',4,'??');
+---------------------+
| LPAD('hi', 4, '??') |
+---------------------+
| ??hi                |
+---------------------+

SELECT LPAD('hi',1,'??');
+---------------------+
| LPAD('hi', 1, '??') |
+---------------------+
| h                   |
+---------------------+

23 - LTRIM

Removes all occurrences of any character present in the specified trim string from the left side of the string.

See also:

Analyze Syntax

func.ltrim(<string>, <trim_string>)

Analyze Examples

func.ltrim('xxdatabend', 'x')
+--------------------------------+
| func.ltrim('xxdatabend', 'x')  |
+--------------------------------+
| databend                       |
+--------------------------------+

SQL Syntax

LTRIM(<string>, <trim_string>)

SQL Examples

SELECT LTRIM('xxdatabend', 'xx'), LTRIM('xxdatabend', 'xy');

┌───────────────────────────────────────────────────────┐
│ ltrim('xxdatabend', 'xx') │ ltrim('xxdatabend', 'xy') │
├───────────────────────────┼───────────────────────────┤
│ databend                  │ databend                  │
└───────────────────────────────────────────────────────┘

24 - MID

Alias for SUBSTR.

25 - NOT LIKE

Pattern not matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

Analyze Syntax

<column>.not_like(<pattern>)

Analyze Examples

my_clothes.not_like('%pants)
+-----------------+
| my_clothes      |
+-----------------+
| plaid pants XL  |
| plaid hat       |
| plaid shirt     |
+-----------------+

SQL Syntax

<expr> NOT LIKE <pattern>

SQL Examples

SELECT name, category FROM system.functions WHERE name like 'tou%' AND name not like '%64' ORDER BY name;
+----------+------------+
| name     | category   |
+----------+------------+
| touint16 | conversion |
| touint32 | conversion |
| touint8  | conversion |
+----------+------------+

26 - NOT REGEXP

Returns 1 if the string expr doesn't match the regular expression specified by the pattern pat, 0 otherwise.

Analyze Syntax

not_(<column>.regexp_match(<pattern>))

Analyze Examples


With an input table of:
+-----------------+
| my_clothes      |
+-----------------+
| plaid pants     |
| plaid hat       |
| plaid shirt     |
| shoes           |
+-----------------+

not_(my_clothes.regexp_match('p*'))
+-------------------------------------+
| not_(my_clothes.regexp_match('p*')) |
+-------------------------------------+
| false                               |
| false                               |
| false                               |
| true                                |
+-------------------------------------+

SQL Syntax

<expr> NOT REGEXP <pattern>

SQL Examples

SELECT 'databend' NOT REGEXP 'd*';
+------------------------------+
| ('databend' not regexp 'd*') |
+------------------------------+
|                            0 |
+------------------------------+

27 - NOT RLIKE

Returns 1 if the string expr doesn't match the regular expression specified by the pattern pat, 0 otherwise.

Analyze Syntax

not_(<column>.regexp_match(<pattern>))

Analyze Examples


With an input table of:
+-----------------+
| my_clothes      |
+-----------------+
| plaid pants     |
| plaid hat       |
| plaid shirt     |
| shoes           |
+-----------------+

not_(my_clothes.regexp_match('p*'))
+-------------------------------------+
| not_(my_clothes.regexp_match('p*')) |
+-------------------------------------+
| false                               |
| false                               |
| false                               |
| true                                |
+-------------------------------------+

SQL Syntax

<expr> NOT RLIKE <pattern>

SQL Examples

SELECT 'databend' not rlike 'd*';
+-----------------------------+
| ('databend' not rlike 'd*') |
+-----------------------------+
|                           0 |
+-----------------------------+

28 - OCT

Returns a string representation of the octal value of N.

Analyze Syntax

func.oct(<expr>)

Analyze Examples

func.oct(12)
+-----------------+
| func.oct(12)    |
+-----------------+
| 014             |
+-----------------+

SQL Syntax

OCT(<expr>)

SQL Examples

SELECT OCT(12);
+---------+
| OCT(12) |
+---------+
| 014     |
+---------+

29 - OCTET_LENGTH

OCTET_LENGTH() is a synonym for LENGTH().

Analyze Syntax

func.octet_length(<str>)

Analyze Examples

func.octet_length('databend')
+-------------------------------+
| func.octet_length('databend') |
+-------------------------------+
|                             8 |
+-------------------------------+

SQL Syntax

OCTET_LENGTH(<str>)

SQL Examples

SELECT OCTET_LENGTH('databend');
+--------------------------+
| OCTET_LENGTH('databend') |
+--------------------------+
|                        8 |
+--------------------------+

30 - ORD

If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function.

If the leftmost character of the string str is a multibyte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

  (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2) ...

Analyze Syntax

func.ord(<str>)

Analyze Examples

func.ord('2')
+----------------+
| func.ord('2)   |
+----------------+
|             50 |
+----------------+

SQL Syntax

ORD(<str>)

Arguments

Arguments	Description
`<str>`	The string.

Return Type

BIGINT

SQL Examples

SELECT ORD('2')
+--------+
| ORD(2) |
+--------+
|     50 |
+--------+

31 - POSITION

POSITION(substr IN str) is a synonym for LOCATE(substr,str). Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.

Analyze Syntax

func.position(<substr>, <str>)

Analyze Examples

func.position('bar', 'foobarbar')
+-----------------------------------+
| func.position('bar', 'foobarbar') |
+-----------------------------------+
|                                 4 |
+-----------------------------------+

SQL Syntax

POSITION(<substr> IN <str>)

Arguments

Arguments	Description
`<substr>`	The substring.
`<str>`	The string.

Return Type

BIGINT

SQL Examples

SELECT POSITION('bar' IN 'foobarbar')
+----------------------------+
| POSITION('bar' IN 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+

SELECT POSITION('xbar' IN 'foobar')
+--------------------------+
| POSITION('xbar' IN 'foobar') |
+--------------------------+
|                        0 |
+--------------------------+

32 - QUOTE

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement.

Analyze Syntax

func.quote(<str>)

Analyze Examples

func.quote('Don\'t')
+----------------------+
| func.quote('Don\'t') |
+----------------------+
|  Don\'t!             |
+----------------------+

SQL Syntax

QUOTE(<str>)

SQL Examples

SELECT QUOTE('Don\'t!');
+-----------------+
| QUOTE('Don't!') |
+-----------------+
| Don\'t!         |
+-----------------+

SELECT QUOTE(NULL);
+-------------+
| QUOTE(NULL) |
+-------------+
|        NULL |
+-------------+

33 - REGEXP

Returns true if the string <expr> matches the regular expression specified by the <pattern>, false otherwise.

Analyze Syntax

<column>.regexp_match(<pattern>)

Analyze Examples


With an input table of:
+-----------------+
| my_clothes      |
+-----------------+
| plaid pants     |
| plaid hat       |
| plaid shirt     |
| shoes           |
+-----------------+

my_clothes.regexp_match('p*')
+-------------------------------+
| my_clothes.regexp_match('p*') |
+-------------------------------+
| true                          |
| true                          |
| true                          |
| false                         |
+-------------------------------+

SQL Syntax

<expr> REGEXP <pattern>

Aliases

RLIKE

SQL Examples

SELECT 'databend' REGEXP 'd*', 'databend' RLIKE 'd*';

┌────────────────────────────────────────────────────┐
│ ('databend' regexp 'd*') │ ('databend' rlike 'd*') │
├──────────────────────────┼─────────────────────────┤
│ true                     │ true                    │
└────────────────────────────────────────────────────┘

34 - REGEXP_INSTR

Returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match. If expr or pat is NULL, the return value is NULL. Character indexes begin at 1.

Analyze Syntax

func.regexp_instr(<expr>, <pat[, pos[, occurrence[, return_option[, match_type]]]]>)

Analyze Examples

func.regexp_instr('dog cat dog', 'dog')
+-----------------------------------------+
| func.regexp_instr('dog cat dog', 'dog') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

SQL Syntax

REGEXP_INSTR(<expr>, <pat[, pos[, occurrence[, return_option[, match_type]]]]>)

Arguments

Arguments	Description
expr	The string expr that to be matched
pat	The regular expression
pos	Optional. The position in expr at which to start the search. If omitted, the default is 1.
occurrence	Optional. Which occurrence of a match to search for. If omitted, the default is 1.
return_option	Optional. Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring's first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.
match_type	Optional. A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

Return Type

A number data type value.

SQL Examples

SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+

SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+

SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+

SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+

35 - REGEXP_LIKE

REGEXP_LIKE function is used to check that whether the string matches the regular expression.

Analyze Syntax

func.regexp_like(<expr>, <pat[, match_type]>)

Analyze Examples

func.regexp_like('a', '^[a-d]')
+---------------------------------+
| func.regexp_like('a', '^[a-d]') |
+---------------------------------+
|                               1 |
+---------------------------------+

SQL Syntax

REGEXP_LIKE(<expr>, <pat[, match_type]>)

Arguments

Arguments	Description
`<expr>`	The string expr that to be matched
`<pat>`	The regular expression
`[match_type]`	Optional. match_type argument is a string that specifying how to perform matching

match_type may contain any or all the following characters:

c: Case-sensitive matching.
i: Case-insensitive matching.
m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
n: The . character matches line terminators. The default is for . matching to stop at the end of a line.
u: Unix-only line endings. Not be supported now.

Return Type

BIGINT Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

SQL Examples

SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+

SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
|                         1 |
+---------------------------+

SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
|                              0 |
+--------------------------------+

SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+-------------------------------------------+
| REGEXP_LIKE('new*
*line', 'new\*.\*line') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line', 'n');
+------------------------------------------------+
| REGEXP_LIKE('new*
*line', 'new\*.\*line', 'n') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

36 - REGEXP_REPLACE

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

Analyze Syntax

func.regexp_replace(<expr>, <pat>, <repl[, pos[, occurrence[, match_type]]]>)

Analyze Examples

func.regexp_replace('a b c', 'b', 'X')
+----------------------------------------+
| func.regexp_replace('a b c', 'b', 'X') |
+----------------------------------------+
| a X c                                  |
+----------------------------------------+

SQL Syntax

REGEXP_REPLACE(<expr>, <pat>, <repl[, pos[, occurrence[, match_type]]]>)

Arguments

Arguments	Description
expr	The string expr that to be matched
pat	The regular expression
repl	The replacement string
pos	Optional. The position in expr at which to start the search. If omitted, the default is 1.
occurrence	Optional. Which occurrence of a match to replace. If omitted, the default is 0 (which means "replace all occurrences").
match_type	Optional. A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

Return Type

VARCHAR

SQL Examples

SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+

SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+

SELECT REGEXP_REPLACE('周 周周 周周周', '周+', 'X', 3, 2);
+-----------------------------------------------------------+
| REGEXP_REPLACE('周 周周 周周周', '周+', 'X', 3, 2)        |
+-----------------------------------------------------------+
| 周 周周 X                                                 |
+-----------------------------------------------------------+

37 - REGEXP_SUBSTR

Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL.

Analyze Syntax

func.regexp_substr(<expr>, <pat[, pos[, occurrence[, match_type]]]>)

Analyze Examples

func.regexp_substr('abc def ghi', '[a-z]+')
+---------------------------------------------+
| func.regexp_substr('abc def ghi', '[a-z]+') |
+---------------------------------------------+
| abc                                         |
+---------------------------------------------+

SQL Syntax

REGEXP_SUBSTR(<expr>, <pat[, pos[, occurrence[, match_type]]]>)

Arguments

Arguments	Description
expr	The string expr that to be matched
pat	The regular expression
pos	Optional. The position in expr at which to start the search. If omitted, the default is 1.
occurrence	Optional. Which occurrence of a match to search for. If omitted, the default is 1.
match_type	Optional. A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

Return Type

VARCHAR

SQL Examples

SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+

SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+

SELECT REGEXP_SUBSTR('周 周周 周周周 周周周周', '周+', 2, 3);
+------------------------------------------------------------------+
| REGEXP_SUBSTR('周 周周 周周周 周周周周', '周+', 2, 3)            |
+------------------------------------------------------------------+
| 周周周周                                                         |
+------------------------------------------------------------------+

38 - REPEAT

Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

Analyze Syntax

func.repeat(<str>, <count>)

Analyze Examples

func.repeat(<str>, <count>)
+-------------------------+
| func.repeat('plaid', 3) |
+-------------------------+
| plaidplaidplaid         |
+-------------------------+

SQL Syntax

REPEAT(<str>, <count>)

Arguments

Arguments	Description
`<str>`	The string.
`<count>`	The number.

SQL Examples

SELECT REPEAT('databend', 3);
+--------------------------+
| REPEAT('databend', 3)    |
+--------------------------+
| databenddatabenddatabend |
+--------------------------+

SELECT REPEAT('databend', 0);
+-----------------------+
| REPEAT('databend', 0) |
+-----------------------+
|                       |
+-----------------------+

SELECT REPEAT('databend', NULL);
+--------------------------+
| REPEAT('databend', NULL) |
+--------------------------+
|                     NULL |
+--------------------------+

39 - REPLACE

Returns the string str with all occurrences of the string from_str replaced by the string to_str.

Analyze Syntax

func.replace(<str>, <from_str>, <to_str>)

Analyze Examples

func.replace(<str>, <from_str>, <to_str>)
+--------------------------------------+
| func.replace('plaidCloud', 'p', 'P') |
+--------------------------------------+
| PlaidCloud                           |
+--------------------------------------+

SQL Syntax

REPLACE(<str>, <from_str>, <to_str>)

Arguments

Arguments	Description
`<str>`	The string.
`<from_str>`	The from string.
`<to_str>`	The to string.

Return Type

VARCHAR

SQL Examples

SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com                    |
+-------------------------------------+

40 - REVERSE

Returns the string str with the order of the characters reversed.

Analyze Syntax

func.reverse(<str>)

Analyze Examples

func.reverse('abc')
+----------------------+
| func..reverse('abc') |
+----------------------+
| cba                  |
+----------------------+

SQL Syntax

REVERSE(<str>)

Arguments

Arguments	Description
`<str>`	The string value.

Return Type

VARCHAR

SQL Examples

SELECT REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+

41 - RIGHT

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

Analyze Syntax

func.right(<str>, <len>)

Analyze Examples

func.right('foobarbar', 4)
+----------------------------+
| func.right('foobarbar', 4) |
+----------------------------+
| rbar                       |
+----------------------------+

SQL Syntax

RIGHT(<str>, <len>);

Arguments

Arguments	Description
`<str>`	The main string from where the character to be extracted
`<len>`	The count of characters

Return Type

VARCHAR

SQL Examples

SELECT RIGHT('foobarbar', 4);
+-----------------------+
| RIGHT('foobarbar', 4) |
+-----------------------+
| rbar                  |
+-----------------------+

42 - RLIKE

Alias for REGEXP.

43 - RPAD

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

Analyze Syntax

func.rpad(<str>, <len>, <padstr>)

Analyze Examples

func.rpad('hi',5,'?')
+-----------------------+
| func.rpad('hi',5,'?') |
+-----------------------+
| hi???                 |
+-----------------------+

func.rpad('hi',1,'?')
+-----------------------+
| func.rpad('hi',1,'?') |
+-----------------------+
| h                     |
+-----------------------+

SQL Syntax

RPAD(<str>, <len>, <padstr>)

Arguments

Arguments	Description
`<str>`	The string.
`<len>`	The length.
`<padstr>`	The pad string.

Return Type

VARCHAR

SQL Examples

SELECT RPAD('hi',5,'?');
+--------------------+
| RPAD('hi', 5, '?') |
+--------------------+
| hi???              |
+--------------------+

SELECT RPAD('hi',1,'?');
+--------------------+
| RPAD('hi', 1, '?') |
+--------------------+
| h                  |
+--------------------+

44 - RTRIM

Removes all occurrences of any character present in the specified trim string from the right side of the string.

See also:

Analyze Syntax

func.rtrim(<string>, <trim_string>)

Analyze Examples

func.rtrim('databend'xx, 'x')
+--------------------------------+
| func.rtrim('databendxx', 'x')  |
+--------------------------------+
| databend                       |
+--------------------------------+

SQL Syntax

RTRIM(<string>, <trim_string>)

SQL Examples

SELECT RTRIM('databendxx', 'x'), RTRIM('databendxx', 'xy');

┌──────────────────────────────────────────────────────┐
│ rtrim('databendxx', 'x') │ rtrim('databendxx', 'xy') │
├──────────────────────────┼───────────────────────────┤
│ databend                 │ databend                  │
└──────────────────────────────────────────────────────┘

45 - SOUNDEX

Generates the Soundex code for a string.

A Soundex code consists of a letter followed by three numerical digits. PlaidCloud Lakehouse's implementation returns more than 4 digits, but you can SUBSTR the result to get a standard Soundex code.
All non-alphabetic characters in the string are ignored.
All international alphabetic characters outside the A-Z range are ignored unless they're the first letter.

Note: What is Soundex? Soundex converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. For more information, see https://en.wikipedia.org/wiki/Soundex

Analyze Syntax

func.soundex(<str>)

Analyze Examples

func.soundex('PlaidCloud Lakehouse')
+--------------------------------------+
| func.soundex('PlaidCloud Lakehouse') |
+--------------------------------------+
| D153                                 |
+--------------------------------------+

SQL Syntax

SOUNDEX(<str>)

Arguments

Arguments	Description
str	The string.

Return Type

Returns a code of type VARCHAR or a NULL value.

SQL Examples

SELECT SOUNDEX('PlaidCloud Lakehouse');

---
D153

-- All non-alphabetic characters in the string are ignored.
SELECT SOUNDEX('PlaidCloud Lakehouse!');

---
D153

-- All international alphabetic characters outside the A-Z range are ignored unless they're the first letter.
SELECT SOUNDEX('PlaidCloud Lakehouse，你好');

---
D153

SELECT SOUNDEX('你好，PlaidCloud Lakehouse');

---
你3153

-- SUBSTR the result to get a standard Soundex code.
SELECT SOUNDEX('databend cloud'),SUBSTR(SOUNDEX('databend cloud'),1,4);

soundex('databend cloud')|substring(soundex('databend cloud') from 1 for 4)|
-------------------------+-------------------------------------------------+
D153243                  |D153                                             |

SELECT SOUNDEX(NULL);
+-------------------------------------+
| `SOUNDEX(NULL)`                     |
+-------------------------------------+
| <null>                              |
+-------------------------------------+

46 - SOUNDS LIKE

Compares the pronunciation of two strings by their Soundex codes. Soundex is a phonetic algorithm that produces a code representing the pronunciation of a string, allowing for approximate matching of strings based on their pronunciation rather than their spelling. PlaidCloud Lakehouse offers the SOUNDEX function that allows you to get the Soundex code from a string.

SOUNDS LIKE is frequently employed in the WHERE clause of SQL queries to narrow down rows using fuzzy string matching, such as for names and addresses, see Filtering Rows in Examples.

Note: While the function can be useful for approximate string matching, it is important to note that it is not always accurate. The Soundex algorithm is based on English pronunciation rules and may not work well for strings from other languages or dialects.

Analyze Syntax

func.sounds_like(<str1>, <str2>)

Analyze Examples

func..sounds_like('Monday', 'Sunday')
+---------------------------------------+
| func..sounds_like('Monday', 'Sunday') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

SQL Syntax

<str1> SOUNDS LIKE <str2>

Arguments

Arguments	Description
str1, 2	The strings you compare.

Return Type

Return a Boolean value of 1 if the Soundex codes for the two strings are the same (which means they sound alike) and 0 otherwise.

SQL Examples

Comparing Strings

SELECT 'two' SOUNDS LIKE 'too'
----
1

SELECT CONCAT('A', 'B') SOUNDS LIKE 'AB';
----
1

SELECT 'Monday' SOUNDS LIKE 'Sunday';
----
0

Filtering Rows

SELECT * FROM  employees;

id|first_name|last_name|age|
--+----------+---------+---+
 0|John      |Smith    | 35|
 0|Mark      |Smythe   | 28|
 0|Johann    |Schmidt  | 51|
 0|Eric      |Doe      | 30|
 0|Sue       |Johnson  | 45|


SELECT * FROM  employees
WHERE  first_name SOUNDS LIKE 'John';

id|first_name|last_name|age|
--+----------+---------+---+
 0|John      |Smith    | 35|
 0|Johann    |Schmidt  | 51|

47 - SPACE

Returns a string consisting of N blank space characters.

Analyze Syntax

func.space(<n>)

Analyze Examples

func.space(20)
+-----------------+
| func.space(20)  |
+-----------------+
|                 |
+-----------------+

SQL Syntax

SPACE(<n>);

Arguments

Arguments	Description
`<n>`	The number of spaces

Return Type

String data type value.

SQL Examples

SELECT SPACE(20)
+----------------------+
| SPACE(20)            |
+----------------------+
|                      |
+----------------------+

48 - SPLIT

Splits a string using a specified delimiter and returns the resulting parts as an array.

Analyze Syntax

func.split('<input_string>', '<delimiter>')

Analyze Examples

func.split('PlaidCloud Lakehouse', ' ')
+-----------------------------------------+
| func.split('PlaidCloud Lakehouse', ' ') |
+-----------------------------------------+
| ['PlaidCloud Lakehouse']          |
+-----------------------------------------+

SQL Syntax

SPLIT('<input_string>', '<delimiter>')

Return Type

Array of strings. SPLIT returns NULL when either the input string or the delimiter is NULL.

SQL Examples

-- Use a space as the delimiter
-- SPLIT returns an array with two parts.
SELECT SPLIT('PlaidCloud Lakehouse', ' ');

split('PlaidCloud Lakehouse', ' ')|
----------------------------------+
['PlaidCloud','Lakehouse']        |

-- Use an empty string as the delimiter or a delimiter that does not exist in the input string
-- SPLIT returns an array containing the entire input string as a single part.
SELECT SPLIT('PlaidCloud Lakehouse', '');

split('databend cloud', '')|
----------------------------------+
['PlaidCloud Lakehouse']    |

SELECT SPLIT('PlaidCloud Lakehouse', ',');

split('databend cloud', ',')|
----------------------------------+
['PlaidCloud Lakehouse']    |

-- Use '	' (tab) as the delimiter
-- SPLIT returns an array with timestamp, log level, and message.

SELECT SPLIT('2023-10-19 15:30:45	INFO	Log message goes here', '	');

split('2023-10-19 15:30:45\tinfo\tlog message goes here', '\t')|
---------------------------------------------------------------+
['2023-10-19 15:30:45','INFO','Log message goes here']         |

49 - SPLIT_PART

Splits a string using a specified delimiter and returns the specified part.

Analyze Syntax

func.split_part('<input_string>', '<delimiter>', '<position>')

Analyze Examples

func.split_part('PlaidCloud Lakehouse', ' ', 1)
+-------------------------------------------------+
| func.split_part('PlaidCloud Lakehouse', ' ', 1) |
+-------------------------------------------------+
| PlaidCloud                                      |
+-------------------------------------------------+

SQL Syntax

SPLIT_PART('<input_string>', '<delimiter>', '<position>')

The position argument specifies which part to return. It uses a 1-based index but can also accept positive, negative, or zero values:

If position is a positive number, it returns the part at the position from the left to the right, or NULL if it doesn't exist.
If position is a negative number, it returns the part at the position from the right to the left, or NULL if it doesn't exist.
If position is 0, it is treated as 1, effectively returning the first part of the string.

Return Type

String. SPLIT_PART returns NULL when either the input string, the delimiter, or the position is NULL.

SQL Examples

-- Use a space as the delimiter
-- SPLIT_PART returns a specific part.
SELECT SPLIT_PART('PlaidCloud Lakehouse', ' ', 1);

split_part('PlaidCloud Lakehouse', ' ', 1)|
------------------------------------------+
PlaidCloud Lakehouse                      |

-- Use an empty string as the delimiter or a delimiter that does not exist in the input string
-- SPLIT_PART returns the entire input string.
SELECT SPLIT_PART('PlaidCloud Lakehouse', '', 1);

split_part('PlaidCloud Lakehouse', '', 1)|
-----------------------------------+
PlaidCloud Lakehouse                     |

SELECT SPLIT_PART('PlaidCloud Lakehouse', ',', 1);

split_part('PlaidCloud Lakehouse', ',', 1)|
------------------------------------+
PlaidCloud Lakehouse                      |

-- Use '    ' (tab) as the delimiter
-- SPLIT_PART returns individual fields.
SELECT SPLIT_PART('2023-10-19 15:30:45   INFO   Log message goes here', '   ', 3);

split_part('2023-10-19 15:30:45   info   log message goes here', '   ', 3)|
--------------------------------------------------------------------------+
Log message goes here                                                     |

-- SPLIT_PART returns an empty string as the specified part does not exist at all.
SELECT SPLIT_PART('2023-10-19 15:30:45   INFO   Log message goes here', '   ', 4);

split_part('2023-10-19 15:30:45   info   log message goes here', '   ', 4)|
--------------------------------------------------------------------------+
                                                                          |

50 - STRCMP

Returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.

Analyze Syntax

func.strcmp(<expr1> ,<expr2>)

Analyze Examples

func.strcmp('text', 'text2')
+------------------------------+
| func.strcmp('text', 'text2') |
+------------------------------+
|                           -1 |
+------------------------------+

func.strcmp('text2', 'text')
+------------------------------+
| func.strcmp('text2', 'text') |
+------------------------------+
|                            1 |
+------------------------------+

func.strcmp('text', 'text')
+------------------------------+
| func.strcmp('text', 'text')  |
+------------------------------+
|                            0 |
+------------------------------+

SQL Syntax

STRCMP(<expr1> ,<expr2>)

Arguments

Arguments	Description
`<expr1>`	The string.
`<expr2>`	The string.

Return Type

BIGINT

SQL Examples

SELECT STRCMP('text', 'text2');
+-------------------------+
| STRCMP('text', 'text2') |
+-------------------------+
|                      -1 |
+-------------------------+

SELECT STRCMP('text2', 'text');
+-------------------------+
| STRCMP('text2', 'text') |
+-------------------------+
|                       1 |
+-------------------------+

SELECT STRCMP('text', 'text');
+------------------------+
| STRCMP('text', 'text') |
+------------------------+
|                      0 |
+------------------------+

51 - SUBSTR

Extracts a string containing a specific number of characters from a particular position of a given string.

The forms without a len argument return a substring from string str starting at position pos.
The forms with a len argument return a substring len characters long from string str, starting at position pos.

It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string. The position of the first character in the string from which the substring is to be extracted is reckoned as 1.

Analyze Syntax

func.substr(<str>, <pos>, <len>)

Analyze Examples

func.substr('Quadratically', 5, 6)
+------------------------------------+
| func.substr('Quadratically', 5, 6) |
+------------------------------------+
| ratica                             |
+------------------------------------+

SQL Syntax

SUBSTR(<str>, <pos>)

SUBSTR(<str>, <pos>, <len>)

Arguments

Arguments	Description
`<str>`	The main string from where the character to be extracted
`<pos>`	The position (starting from 1) the substring to start at. If negative, counts from the end
`<len>`	The maximum length of the substring to extract

Aliases

Return Type

VARCHAR

SQL Examples

SELECT
  SUBSTRING('Quadratically', 5),
  SUBSTR('Quadratically', 5),
  MID('Quadratically', 5);

┌─────────────────────────────────────────────────────────────────────────────────────────────────┐
│ substring('quadratically' from 5) │ substring('quadratically' from 5) │ mid('quadratically', 5) │
├───────────────────────────────────┼───────────────────────────────────┼─────────────────────────┤
│ ratically                         │ ratically                         │ ratically               │
└─────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT
  SUBSTRING('Quadratically', 5, 6),
  SUBSTR('Quadratically', 5, 6),
  MID('Quadratically', 5, 6);

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ substring('quadratically' from 5 for 6) │ substring('quadratically' from 5 for 6) │ mid('quadratically', 5, 6) │
├─────────────────────────────────────────┼─────────────────────────────────────────┼────────────────────────────┤
│ ratica                                  │ ratica                                  │ ratica                     │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

52 - SUBSTRING

Alias for SUBSTR.

53 - TO_BASE64

Converts the string argument to base-64 encoded form and returns the result as a character string. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL.

Analyze Syntax

func.to_base64(<v>)

Analyze Examples

func.to_base64('abc')
+-----------------------+
| func.to_base64('abc') |
+-----------------------+
| YWJj                  |
+-----------------------+

SQL Syntax

TO_BASE64(<v>)

Arguments

Arguments	Description
`<v>`	The value.

Return Type

VARCHAR

SQL Examples

SELECT TO_BASE64('abc');
+------------------+
| TO_BASE64('abc') |
+------------------+
| YWJj             |
+------------------+

54 - TRANSLATE

Transforms a given string by replacing specific characters with corresponding replacements, as defined by the provided mapping.

Analyze Syntax

func.translate('<inputString>', '<charactersToReplace>', '<replacementCharacters>')

Analyze Examples

func.translate('databend', 'de', 'DE')
+----------------------------------------+
| func.translate('databend', 'de', 'DE') |
+----------------------------------------+
| DatabEnD                               |
+----------------------------------------+

SQL Syntax

TRANSLATE('<inputString>', '<charactersToReplace>', '<replacementCharacters>')

Parameter	Description
`<inputString>`	The input string to be transformed.
`<charactersToReplace>`	The string containing characters to be replaced in the input string.
`<replacementCharacters>`	The string containing replacement characters corresponding to those in `<charactersToReplace>`.

SQL Examples

-- Replace 'd' with '$' in 'databend'
SELECT TRANSLATE('databend', 'd', '$');

---
$ataben$

-- Replace 'd' with 'D' in 'databend'
SELECT TRANSLATE('databend', 'd', 'D');

---
DatabenD

-- Replace 'd' with 'D' and 'e' with 'E' in 'databend'
SELECT TRANSLATE('databend', 'de', 'DE');

---
DatabEnD

-- Remove 'd' from 'databend'
SELECT TRANSLATE('databend', 'd', '');

---
ataben

55 - TRIM

Returns the string without leading or trailing occurrences of the specified remove string. If remove string is omitted, spaces are removed.

The Analyze function automatically trims both leading and trailing spaces.

Analyze Syntax

func.trim(str)

Analyze Examples

func.trim('   plaidcloud   ')
+--------------------------------+
| func.trim('   plaidcloud   ') |
+--------------------------------+
| 'plaidcloud'                   |
+--------------------------------+

SQL Syntax

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str)

SQL Examples

Please note that ALL the examples in this section will return the string 'databend'.

The following example removes the leading and trailing string 'xxx' from the string 'xxxdatabendxxx':

SELECT TRIM(BOTH 'xxx' FROM 'xxxdatabendxxx');

The following example removes the leading string 'xxx' from the string 'xxxdatabend':

SELECT TRIM(LEADING 'xxx' FROM 'xxxdatabend' );

The following example removes the trailing string 'xxx' from the string 'databendxxx':

SELECT TRIM(TRAILING 'xxx' FROM 'databendxxx' );

If no remove string is specified, the function removes all leading and trailing spaces. The following examples remove the leading and/or trailing spaces:

SELECT TRIM('   databend   ');
SELECT TRIM('   databend');
SELECT TRIM('databend   ');

56 - TRIM_BOTH

Removes all occurrences of the specified trim string from the beginning, end, or both sides of the string.

Analyze Syntax

func.trim_both(<string>, <trim_string>)

Analyze Examples

func.trim_both('xxdatabendxx', 'x')
+--------------------------------------+
| func.trim_both('xxdatabendxx', 'x')  |
+--------------------------------------+
| databend                             |
+--------------------------------------+

SQL Syntax

TRIM_BOTH(<string>, <trim_string>)

SQL Examples

SELECT TRIM_BOTH('xxdatabendxx', 'xxx'), TRIM_BOTH('xxdatabendxx', 'xx'), TRIM_BOTH('xxdatabendxx', 'x');

┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ trim_both('xxdatabendxx', 'xxx') │ trim_both('xxdatabendxx', 'xx') │ trim_both('xxdatabendxx', 'x') │
├──────────────────────────────────┼─────────────────────────────────┼────────────────────────────────┤
│ xxdatabendxx                     │ databend                        │ databend                       │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘

57 - TRIM_LEADING

Removes all occurrences of the specified trim string from the beginning of the string.

See also:

Analyze Syntax

func.trim_leading(<string>, <trim_string>)

Analyze Examples

func.trim_leading('xxdatabendxx', 'x')
+------------------------------------------+
| func.trim_leading('xxdatabendxx', 'x')   |
+------------------------------------------+
| databendxx                               |
+------------------------------------------+

SQL Syntax

TRIM_LEADING(<string>, <trim_string>)

SQL Examples

SELECT TRIM_LEADING('xxdatabend', 'xxx'), TRIM_LEADING('xxdatabend', 'xx'), TRIM_LEADING('xxdatabend', 'x');

┌────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ trim_leading('xxdatabend', 'xxx') │ trim_leading('xxdatabend', 'xx') │ trim_leading('xxdatabend', 'x') │
├───────────────────────────────────┼──────────────────────────────────┼─────────────────────────────────┤
│ xxdatabend                        │ databend                         │ databend                        │
└────────────────────────────────────────────────────────────────────────────────────────────────────────┘

58 - TRIM_TRAILING

Removes all occurrences of the specified trim string from the end of the string.

See also:

Analyze Syntax

func.trim_trailing(<string>, <trim_string>)

Analyze Examples

func.trim_trailing('xxdatabendxx', 'x')
+------------------------------------------+
| func.trim_trailing('xxdatabendxx', 'x')  |
+------------------------------------------+
| xxdatabend                               |
+------------------------------------------+

SQL Syntax

TRIM_TRAILING(<string>, <trim_string>)

SQL Examples

SELECT TRIM_TRAILING('databendxx', 'xxx'), TRIM_TRAILING('databendxx', 'xx'), TRIM_TRAILING('databendxx', 'x');

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ trim_trailing('databendxx', 'xxx') │ trim_trailing('databendxx', 'xx') │ trim_trailing('databendxx', 'x') │
├────────────────────────────────────┼───────────────────────────────────┼──────────────────────────────────┤
│ databendxx                         │ databend                          │ databend                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

59 - UCASE

Alias for UPPER.

60 - UNHEX

For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

Analyze Syntax

func.unhex(<expr>)

Analyze Examples

func.unhex('6461746162656e64')
+--------------------------------+
| func.unhex('6461746162656e64') |
+--------------------------------+
| 6461746162656E64               |
+--------------------------------+

SQL Syntax

UNHEX(<expr>)

Aliases

FROM_HEX

SQL Examples

SELECT UNHEX('6461746162656e64') as c1, typeof(c1),UNHEX('6461746162656e64')::varchar as c2, typeof(c2), FROM_HEX('6461746162656e64');

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│           c1              │     typeof(c1)         │       c2         |    typeof(c2)     |   from_hex('6461746162656e64')  |
├───────────────────────────┼────────────────────────|──────────────────┤───────────────────|─────────────────────────────────┤
│ 6461746162656E64          │      binary            │      databend    |    varchar        |   6461746162656E64              |
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT UNHEX(HEX('string')), unhex(HEX('string'))::varchar;

┌──────────────────────────────────────────────────────┐
│ unhex(hex('string')) │ unhex(hex('string'))::varchar │
├──────────────────────┼───────────────────────────────┤
│ 737472696E67         │ string                        │
└──────────────────────────────────────────────────────┘

61 - UPPER

Returns a string with all characters changed to uppercase.

Analyze Syntax

func.unhex(<expr>)

Analyze Examples

func.upper('hello, plaidcloud lakehouse!')
+--------------------------------------------+
| func.upper('hello, plaidcloud lakehouse!') |
+--------------------------------------------+
| 'HELLO, PLAIDCLOUD LAKEHOUSE!'             |
+--------------------------------------------+

SQL Syntax

UPPER(<str>)

Aliases

UCASE

Return Type

VARCHAR

SQL Examples

SELECT UPPER('hello, databend!'), UCASE('hello, databend!');

┌───────────────────────────────────────────────────────┐
│ upper('hello, databend!') │ ucase('hello, databend!') │
├───────────────────────────┼───────────────────────────┤
│ HELLO, DATABEND!          │ HELLO, DATABEND!          │
└───────────────────────────────────────────────────────┘