Character Processing Functions and Operators

bit_length(string)

Description: Returns the length of the string expression in bits (bytes x 8). Its result depends on the database encoding mode.

Return type: integer

Example:

1
2
3
4
5

SELECT bit_length('world');
 bit_length
------------
         40
(1 row)

btrim(string text [, characters text])

Description: Removes the specified characters from both ends of the string.

Return type: text

Parameters:

• string: (mandatory) target string.
• characters: (optional) characters to be removed from both ends of the target string. If it is not specified, all whitespace characters are removed by default.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT btrim('sring' , 'ing');
 btrim
-------
 sr
(1 row)

SELECT btrim('   sting  ');
 btrim
-------
 sting
(1 row)

char_length(string) or character_length(string)

Description: Obtains the number of characters in a string.

Return type: integer

For multi-byte character encoding, one character may occupy multiple bytes.

Example:

1
2
3
4
5

SELECT char_length('hello');
 char_length
-------------
           5
(1 row)

instr(text,text,int,int)

Description: Obtains the position of a substring in the target string. If no match is found, 0 is returned.

• First text: (mandatory) target string.
• Second text: (mandatory) substring to be matched.
• First int: start position of the matching. If this parameter is not set, the matching starts from the beginning by default.
• Second int: which occurrence of the substring the function should return the position for. If this parameter is not set, the first occurrence is used by default.

Return type: integer

Example:

Obtain the position of the 2nd occurrence of bcd starting from position 2 in the main string abcdabcdabcd.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
 instr
-------
     6
(1 row)

SELECT instr( 'abcdabcdabcd', 'lmk', 2);
 instr
-------
     0
(1 row)

lengthb(text/bpchar)

Description: Returns the number of bytes of a specified string.

Return type: integer

Example:

1
2
3
4
5

SELECT lengthb('hello');
 lengthb
---------
       5
(1 row)

• For a string containing newline characters, for example, a string consisting of a newline character and a space, the value of length and lengthb in DWS is 2.
• This function returns the number of bytes in a specified string. For multi-byte character encoding, one character may occupy multiple bytes.

left(str text, n int)

Description: Returns first n characters in the string.

• In the ORA- or TD-compatible mode, all but the last |n| characters are returned if n is negative.
• In the MySQL-compatible mode, an empty string is returned if n is negative.

Return type: text

Example:

1
2
3
4
5

 SELECT left('database', 4);
 left
------
 data
(1 row)

length(string bytea, encoding name)

Description: Obtains the number of characters in a string in the specified encoding format. In the specified encoding format, string must be valid.

Return type: integer

Example:

1
2
3
4
5

SELECT length('database', 'UTF8');
 length
--------
      8
(1 row)

lpad(string text, length int [, fill text])

Description: Fills up the string to the specified length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).

Return type: text

Example:

1
2
3
4
5

SELECT lpad('data', 8, 'xy');
   lpad
----------
 xyxydata
(1 row)

octet_length(string)

Description: Obtains the number of bytes in a string. It is important for processing multi-byte characters (such as UTF-8 characters and special symbols) and can accurately obtain the storage size of a string.

Return type: integer

Example:

1
2
3
4
5

SELECT octet_length('data');
 octet_length
--------------
            4
(1 row)

overlay(string placing string FROM int [for int])

Description: Replaces the part of a string starting from a specified position with a specified string. It can flexibly replace substring.

Parameters:

FROM int: starts the replacement from the nth character of the first string.

for int: (optional) number of characters to be replaced in the first string. If this parameter is not specified, all characters starting from from are replaced by default.

Return type: text

Example: Replace the substring of the original string starting from position 3 with xyz. The result is abxyzef.

1
2
3
4
5

SELECT overlay('abcdef' placing 'xyz' FROM 3);
 overlay
---------
 abxyzf
(1 row)

In the string hello, replace three consecutive characters starting from the second character with world. The result is hworldo.

1
2
3
4
5

SELECT overlay('hello' placing 'world' from 2 for 3 );
 overlay 
---------
 hworldo
(1 row)

position(substring in string)

Description: Returns the position of a specified substring in a string. If the string does not contain substrings, 0 is returned.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT position('ing' in 'string');
 position
----------
        4
(1 row)

SELECT position('ing' in 'strin');
 position
----------
       0
(1 row)

pg_client_encoding()

Description: Obtains the character encoding mode of the current client. If a character display problem occurs, you can check the client encoding to quickly locate the problem.

Return type: name

Example:

1
2
3
4
5

SELECT pg_client_encoding();
 pg_client_encoding
--------------------
 UTF8
(1 row)

quote_ident(string text)

Description: Returns the given string suitably quoted to be used as an identifier in an SQL statement string (quotation marks are used as required). Quotes are added only if necessary (that is, if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

Return type: text

Example:

1
2
3
4
5

SELECT quote_ident('hello world');
 quote_ident
--------------
 "hello world"
(1 row)

quote_literal(string text)

Description: Returns the given string suitably quoted to be used as a string literal in an SQL statement string (quotation marks are used as required).

Return type: text

Example:

1
2
3
4
5

SELECT quote_literal('hello');
 quote_literal 
---------------
 'hello'
(1 row)

If command similar to the following exists, text will be escaped.

1
2
3
4
5

SELECT quote_literal(E'O\'hello');
 quote_literal
---------------
 'O''hello'
(1 row)

If command similar to the following exists, backslash will be properly doubled.

1
2
3
4
5

SELECT quote_literal('O\hello');
 quote_literal 
---------------
 E'O\\hello'
(1 row)

If the parameter is null, return NULL. If the parameter may be null, you are advised to use quote_nullable.

1
2
3
4
5

SELECT quote_literal(NULL);
 quote_literal 
---------------

(1 row)

quote_literal(value anyelement)

Description: Converts the given value to text and then quotes it as a literal.

Return type: text

Example:

1
2
3
4
5

SELECT quote_literal(42.5);
 quote_literal 
---------------
 '42.5'
(1 row)

If command similar to the following exists, the given value will be escaped.

1
2
3
4
5

SELECT quote_literal(E'O\'42.5');
 quote_literal
---------------
 'O''42.5'
(1 row)

If command similar to the following exists, backslash will be properly doubled.

1
2
3
4
5

SELECT quote_literal('O\42.5');
 quote_literal 
---------------
 E'O\\42.5'
(1 row)

quote_nullable(string text)

Description: Returns the given string suitably quoted to be used as a string literal in an SQL statement string (quotation marks are used as required).

Return type: text

Example:

1
2
3
4
5

SELECT quote_nullable('hello');
 quote_nullable 
----------------
 'hello'
(1 row)

If command similar to the following exists, text will be escaped.

1
2
3
4
5

SELECT quote_nullable(E'O\'hello');
 quote_nullable
----------------
 'O''hello'
(1 row)

If command similar to the following exists, backslash will be properly doubled.

1
2
3
4
5

SELECT quote_nullable('O\hello');
 quote_nullable
----------------
 E'O\\hello'
(1 row)

If the parameter is null, return NULL.

1
2
3
4
5

SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

quote_nullable(value anyelement)

Description: Converts the given value to text and then quotes it as a literal.

Return type: text

Example:

1
2
3
4
5

SELECT quote_nullable(42.5);
 quote_nullable
----------------
 '42.5'
(1 row)

If command similar to the following exists, the given value will be escaped.

1
2
3
4
5

SELECT quote_nullable(E'O\'42.5');
 quote_nullable 
----------------
 'O''42.5'
(1 row)

If command similar to the following exists, backslash will be properly doubled.

1
2
3
4
5

SELECT quote_nullable('O\42.5');
 quote_nullable
----------------
 E'O\\42.5'
(1 row)

If the parameter is null, return NULL.

1
2
3
4
5

SELECT quote_nullable(NULL);
 quote_nullable
----------------
 NULL
(1 row)

substring(string [from int] [for int])

Description: Extracts a substring. from int indicates the start position of the truncation. for int indicates the number of characters truncated.

Return type: text

Example:

Extract three characters starting from the second character h in the string Thomas, that is, hom.

1
2
3
4
5

SELECT substring('Thomas' from 2 for 3);
 substring
-----------
 hom
(1 row)

substring(string from pattern)

Description: Extracts substring matching POSIX regular expression. It returns the text that matches the pattern. If no match record is found, a null value is returned.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT substring('Thomas' from '...$');
 substring
-----------
 mas
(1 row)
SELECT substring('foobar' from 'o(.)b');
 substring 
--------
 o
(1 row)
SELECT substring('foobar' from '(o(.)b)');
 substring 
--------
 oob
(1 row)

If the POSIX pattern contains any parentheses, the portion of the text that matched the first parenthesized sub-expression (the one whose left parenthesis comes first) is returned. You can put parentheses around the whole expression if you want to use parentheses within it without triggering this exception.

substring(string from pattern for escape)

Description: Extracts substring matching SQL regular expression. The specified pattern must match the entire data string, or else the function fails and returns null. To indicate the part of the pattern that should be returned on success, the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned.

Return type: text

Example:

1
2
3
4
5

SELECT substring('Thomas' from '%#"o_a#"_' for '#');
 substring
-----------
 oma
(1 row)

rawcat(raw,raw)

Description: Concatenates the given strings.

Return type: raw

Example:

1
2
3
4
5

SELECT rawcat('ab','cd');
 rawcat
--------
 ABCD
(1 row)

regexp_like(text,text,text)

Description: Performs a regular expression matching.

Return type: bool

Example:

1
2
3
4
5

SELECT regexp_like('str','[ac]');
 regexp_like
-------------
 f
(1 row)

regexp_substr(text,text)

Description: Extracts substrings from a regular expression. Its function is similar to substr. When a regular expression contains multiple parallel brackets, it also needs to be processed.

Return type: text

Example:

1
2
3
4
5

SELECT regexp_substr('str','[ac]');
 regexp_substr
---------------

(1 row)

regexp_matches(string text, pattern text [, flags text])

Description: Returns all captured substrings resulting from matching a POSIX regular expression against the string. If the pattern does not match, the function returns no rows. If the pattern contains no parenthesized sub-expressions, then each row returned is a single-element text array containing the substring matching the whole pattern. If the pattern contains parenthesized sub-expressions, the function returns a text array whose nth element is the substring matching the nth parenthesized sub-expression of the pattern.

The optional flags argument contains zero or multiple single-letter flags that change function behavior. i indicates that the matching is not related to uppercase and lowercase. g indicates that each matching substring is replaced, instead of replacing only the first one.

If the last parameter is provided but the parameter value is an empty string ('') and the SQL compatibility mode of the database is set to ORA, the returned result is an empty set. This is because the ORA compatible mode treats the empty string ('') as NULL. To resolve this problem, you can:

• Change the database SQL compatibility mode to TD.
• Do not provide the last parameter or do not set the last parameter to an empty string.

Return type: setof text[]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
    regexp_matches    
--------------
 {bar,beque}
 {bazil,barf}
(2 rows)

If there is no subquery, the table data will not be displayed if there is no match for the regexp_matches function. This outcome is generally undesirable and should be avoided. It is recommended to use the regexp_substr function to achieve the same functionality.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT * FROM tab;
 c1  | c2  
-----+-----
 dws | dws
(1 row)

SELECT c1, regexp_matches(c2, '(bar)(beque)') FROM tab;
 c1 | regexp_matches 
----+----------------
(0 rows)

SELECT c1, c2, (SELECT regexp_matches(c2, '(bar)(beque)')) FROM tab;
 c1  | c2  | regexp_matches 
-----+-----+----------------
 dws | dws | 
(1 row)

regexp_split_to_array(string text, pattern text [, flags text ])

Description: Splits string using a POSIX regular expression as the delimiter. The regexp_split_to_array function behaves the same as regexp_split_to_table, except that regexp_split_to_array returns its result as an array of text.

Return type: text[]

Example:

1
2
3
4
5

SELECT regexp_split_to_array('hello world', E'\\s+');
 regexp_split_to_array
-----------------------
 {hello,world}
(1 row)

regexp_split_to_table(string text, pattern text [, flags text])

Description: Splits string using a POSIX regular expression as the delimiter. If there is no match to the pattern, the function returns the string. If there is at least one match, for each match it returns the text from the end of the last match (or the beginning of the string) to the beginning of the match. When there are no more matches, it returns the text from the end of the last match to the end of the string.

The flags parameter is a text string containing zero or more single-letter flags that change the function's behavior. i indicates that the matching is not related to uppercase and lowercase. g indicates that each matching substring is replaced, instead of replacing only the first one.

Return type: setof text

Example:

1
2
3
4
5
6

SELECT regexp_split_to_table('hello world', E'\\s+');
 regexp_split_to_table
-----------------------
 hello
 world
(2 rows)

repeat(string text, number int)

Description: Outputs a string repeatedly for a specified number of times. If number int is set to zero or a negative value, an empty string is returned.

Return type: text

Example:

1
2
3
4
5

SELECT repeat('abc', 4);
    repeat
--------------
 abcabcabcabc
(1 row)

replace(string text, from text, to text)

Description: Replace all occurrences in string of substring from with substring to.

The case is sensitive, so you need to ensure that the case of from text and to text matches that of the original string.

Return type: text

Example:

1
2
3
4
5

SELECT replace('databasedata', 'data', 'xxx');
  replace
------------
 xxxbasexxx
(1 row)

reverse(str)

Description: Returns a string in reverse order.

Return type: text

Example:

1
2
3
4
5

SELECT reverse('abcde');
 reverse
---------
 edcba
(1 row)

right(str text, n int)

Description: Returns the last n characters in the string.

• In the ORA- or TD-compatible mode, all but the last |n| characters are returned if n is negative.
• In the MySQL-compatible mode, an empty string is returned if n is negative.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT right('abcde', 2);
 right
-------
 de
(1 row)

SELECT right('abcde', -2);
 right 
-------
 cde
(1 row)

rpad(string text, length int [, fill text])

Description: Fills up string to length by appending the characters fill. If the string is longer than the specified length, the string is truncated.

Parameters:

• string: original string to be processed.
• length: length of the target string.
• fill (optional): character or string used for filling. The default value is a space.

Return type: text

Example:

Fill the original string (data) of length 4 to a target length of 10 using the fill characters xy.

1
2
3
4
5

SELECT rpad('data', 10, 'xy');
    rpad
------------
 dataxyxyxy
(1 row)

Fill the original string database using the fill characters xy. The string database is longer than the target length 4, and the string is truncated from the end. The returned string is data.

1
2
3
4
5

SELECT rpad('database',4,'xy');
 rpad
------
 data
(1 row)

rtrim(string text [, characters text])

Description: Removes the specified characters from the right (end) of a string.

Parameters:

• string: original string to be processed.
• characters (optional): set of characters to be removed from the right of the string. By default, all blank characters (for example, spaces) are removed.

Return type: text

Example:

If the parameter characters is not specified, all blank characters on the right of the string Database are removed by default.

1
2
3
4
5

SELECT rtrim('Database    ');
  rtrim
----------
 Database
(1 row)

Removes the specified character x from the string.

1
2
3
4
5

SELECT rtrim('trimxxxx', 'x');
 rtrim
-------
 trim
(1 row)

substrb(text,int,int)

Description: Extracts a substring within a string. The first int indicates the start position of the subtraction. The second int indicates the number of bytes subtracted.

Return type: text

Example:

1
2
3
4
5

SELECT substrb('string',2,3);
 substrb
---------
 tri
(1 row)

substrb(text,int)

Description: Extracts a substring within a string. int indicates the start position of the subtraction.

Return type: text

Example:

1
2
3
4
5

SELECT substrb('string',2);
 substrb
---------
 tring
(1 row)

string || string

Description: Concatenates strings.

Return type: text

Example:

1
2
3
4
5

SELECT 'DA'||'TABASE' AS RESULT;
 result 
----------
 DATABASE
(1 row)

string || non-string or non-string || string

Description: Concatenates strings and non-strings.

Return type: text

Example:

1
2
3
4
5

SELECT 'Value: '||123 AS RESULT;
   result
------------
 Value: 123
(1 row)

split_part(string text, delimiter text, field int)

Description: Splits the original string into several parts based on the specified delimiter (delimiter parameter) and returns the given field (field parameter). This function is used to extract specific fields from composite strings.

Parameters:

• string: original string to be split.
• delimiter: used to separate the string.
• field: given field returned after the split. The value starts from 1.

Return type: text

Example:

Split the string abc~@~def~@~ghi by ~@~ to obtain three parts (abc, def, and ghi), and return the second part, that is, def.

1
2
3
4
5

SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
 split_part
------------
 def
(1 row)

strpos(string, substring)

Description: Obtains the position of the first occurrence of the specified substring in the target string. If the substring does not exist in the target string, 0 is returned.

Return type: integer

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT strpos('source', 'rc');
 strpos
--------
      4
(1 row)

SELECT strpos('source', 'data');
 strpos
--------
      0
(1 row)

to_hex(number int or bigint)

Description: Converts a number to a hexadecimal number.

Return type: text

Example:

1
2
3
4
5

SELECT to_hex(2147483647);
  to_hex
----------
 7fffffff
(1 row)

translate(string text, from text, to text)

Description: Replaces any character in the original string that matches a character in the from set by the corresponding character in the to set. If from is longer than to, extra characters occurred in from are removed.

Parameters:

• string: original string to be processed.
• from: specifies the characters to be replaced.
• to: specifies the characters after replacement.

Return type: text

Example:

Set the from parameter to 143 and the to parameter ax. Replace 1 and 4 in the string 12345 with a and x respectively. Delete 3 because there is no corresponding replacement character. a2x5 is returned.

1
2
3
4
5

SELECT translate('12345', '143', 'ax');
 translate
-----------
 a2x5
(1 row)

length(string)

Description: Obtains the character length (number of characters) of a string.

Return type: integer

Example:

1
2
3
4
5

SELECT length('database');
 length 
--------
      8
(1 row)

lengthb(string)

Description: Obtains the length of a string in bytes. The value depends on character sets (GBK and UTF8).

Return type: integer

Example:

1
2
3
4
5

SELECT lengthb('hello');
 lengthb 
---------
       5
(1 row)

substr(string,from)

Description: Truncates a specified substring from a string.

from indicates the start position of the substring.

• If from starts at 0, the value 1 is used.
• If from is a positive value, all characters from from to the end are extracted.
• If from is a negative value, the last n characters in the string are extracted, in which n indicates the absolute value of from.

Return type: varchar

Example:

When from starts at 0, extract all characters from the first character to the end of the string database.

1
2
3
4
5

SELECT substr('database',0);
  substr
----------
 database
(1 row)

When from is a positive value, extract all characters from the fifth character to the end of the string database.

1
2
3
4
5

SELECT substr('database',5);
 substr
--------
 base
(1 row)

When from is a negative value, extract all characters from the fourth character from the end to the end of the string database.

1
2
3
4
5

SELECT substr('database',-4);
 substr
--------
 base
(1 row)

substr(string,from,count)

Description: Truncates a substring of a specified length from a string.

from indicates the start position of the substring. count indicates the length of the extracted substring.

• If from starts at 0, the value 1 is used.
• If from is a positive value, extract count characters starting from from.
• If from is a negative value, extract the last n count characters in the string, in which n indicates the absolute value of from.
• When count is less than 1, null is returned.

Return type: varchar

Example:

When from starts at 0, extract the first four characters from the string database.

1
2
3
4
5

SELECT substr('database',0,4);
 substr
--------
 data
(1 row)

When from is a positive number, extract the first four characters from the fifth character of the string database.

1
2
3
4
5

SELECT substr('database',5,4);
 substr
--------
 base
(1 row)

If from is a negative number, truncate three characters starting from the last fourth character of database.

1
2
3
4
5

SELECT substr('database',-4,3);
 substr
--------
 bas
(1 row)

substrb(string,from)

Description: The functionality of this function is the same as that of SUBSTR(string,from). However, the calculation unit is byte.

Return type: bytea

Example:

1
2
3
4
5

SELECT substrb('ABCDEF',-2);
 substrb 
---------
 EF
(1 row)

substrb(string,from,count)

Description: The functionality of this function is the same as that of SUBSTR(string,from,count). However, the calculation unit is byte.

Return type: bytea

Example:

1
2
3
4
5

SELECT substrb('ABCDEF',2,2);
 substrb 
---------
 BC
(1 row)

trim([leading |trailing |both] [characters] from string)

Description: Deletes specific characters from the beginning, end, or both ends of a string.

Parameters:

• leading: deletes spaces or specified characters at the beginning of a string.
• trailing: deletes spaces or specified characters at the end of a string.
• both: deletes spaces or specified characters at the beginning and end of a string. (It is a default behavior.)
• characters: (optional) specifies specific characters to be deleted. If this parameter is not specified, spaces are deleted by default.
• string: original string to be processed.

Return type: varchar

Example:

Deletes the characters x at the beginning and end of the string xTomxx.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT trim(BOTH 'x' FROM 'xTomxx');
 btrim
-------
 Tom
(1 row)

SELECT trim('x' FROM 'xTomxx');
 btrim
-------
 Tom
(1 row)

Delete the character x at the beginning of the string xTomxx.

1
2
3
4
5

SELECT trim(LEADING 'x' FROM 'xTomxx');
 ltrim
-------
 Tomxx
(1 row)

Delete the character x at the end of the string xTomxx.

1
2
3
4
5

SELECT trim(TRAILING 'x' FROM 'xTomxx');
 rtrim
-------
 xTom
(1 row)

rtrim(string [, characters])

Description: Deletes the specified characters from the end of a string.

Parameters:

• string: original string to be processed.
• characters (optional): specifies the set of characters to be deleted from the end of a string. By default, all blank characters (for example, spaces) are deleted.

Return type: varchar

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT rtrim('DataxBasexx','x');
   rtrim
-----------
 DataxBase
(1 row)

SELECT rtrim('   Data  ');
  rtrim
---------
    Data
(1 row)

ltrim(string [, characters])

Description: Deletes the specified characters from the beginning of a string.

Parameters:

• string: original string to be processed.
• characters (optional): specifies the set of characters to be deleted from the beginning of a string. By default, all blank characters (for example, spaces) are deleted.

Return type: varchar

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT ltrim('xxDataxBase','x');
   ltrim
-----------
 DataxBase
(1 row)

SELECT ltrim('   Data  ');
 ltrim
--------
 Data
(1 row)

upper(string)

Description: Converts the string into the uppercase.

Return type: varchar

Example:

1
2
3
4
5

SELECT upper('database');
  upper
----------
 DATABASE
(1 row)

ucase(string)

Description: Converts the string into the uppercase.

Return type: varchar

Example:

SELECT ucase('database');
  ucase
----------
 DATABASE
(1 row)

lower(string)

Description: Converts the string into the lowercase.

Return type: varchar

Example:

1
2
3
4
5

SELECT lower('DATABASE');
  lower
----------
 database
(1 row)

lcase(string)

Description: Converts the string into the lowercase.

Return type: varchar

Example:

SELECT lcase('DATABASE');
  lcase
----------
 database
(1 row)

initcap(string)

Description: Converts the first letter of each word in the string into the uppercase and the other letters into the lowercase.

Return type: text

Example:

1
2
3
4
5

SELECT initcap('hi THOMAS');
  initcap
-----------
 Hi Thomas
(1 row)

rpad(string varchar, length int [, fill varchar])

Description: Adds specified characters to the right of a string until the string reaches the specified length. If the string is longer than the specified length, the string is truncated.

Parameters:

• string: original string to be processed.
• length: length of the target string. length in DWS indicates the character length. One Chinese character is counted as one character.
• fill (optional): character or string used for filling. The default value is a space.

Return type: varchar

Example:

1
2
3
4
5

SELECT rpad('data',10,'xyz');
 rpad
------------
 dataxyzxyz
(1 row)

1
2
3
4
5

SELECT rpad('hi',5,'abcdefg');
 rpad  
-------
 hiabc
(1 row)

instr(string,substring[,position,occurrence])

Description: Queries and returns the value of the substring position that occurs the occurrence (first by default) times from the position (1 by default) in the string.

• If the value of position is 0, 0 is returned.
• If the value of position is negative, searches backwards from the last nth character in the string, in which n indicates the absolute value of position.

In this function, the calculation unit is character. One Chinese character is one character.

Return type: integer

Example:

1
2
3
4
5

SELECT instr('corporate floor','or', 3);
 instr 
-------
     5
(1 row)

1
2
3
4
5

SELECT instr('corporate floor','or',-3,2);
 instr 
-------
     2
(1 row)

locate(substring,string[,position])

Description: From the specified position (1 by default) in the string on, queries and returns the value of position where the substring occurs for the first time. The unit is character. If the string does not contain substrings, 0 is returned.

Return type: integer

Example:

1
2
3
4
5

SELECT locate('ball','football');
 locate 
--------
     5
(1 row)

1
2
3
4
5

SELECT locate('er','soccerplayer','6');
 locate 
--------
    11 
(1 row)

ascii(string)

Description: Returns the ASCII code of the first character in a string.

Return type: integer

Example:

Return the ASCII code of the first character x in the string xyz.

1
2
3
4
5

SELECT ascii('xyz');
 ascii 
-------
   120
(1 row)

replace(string varchar, search_string varchar, replacement_string varchar)

Description: Searches for a specific substring (search_string) in a string and replaces it with another string (replacement_string).

Parameters:

• string: original string to be processed.
• search_string: substring to be replaced.
• replacement_string: new substring used to replace search_string.

Return type: varchar

Example:

Replace all substrings ab in the string ababab and mabab with cd.

1
2
3
4
5

SELECT replace('ababab and mabab','ab','cd');
     replace
------------------
 cdcdcd and mcdcd
(1 row)

lpad(string varchar, length int[, repeat_string varchar])

Description: Adds specified characters to the left of a string until the string reaches the specified length. If the length of the original string is greater than the target length (length parameter), the target string is truncated from the right to a string of the length.

Parameters:

• string: original string to be padded.
• length: length of the target string.
• repeat_string: (optional) characters used for padding. By default, spaces are used for padding.

Return type: varchar

Example:

Set original string length (base) to 4 and the target length to 8, and use asterisks (*) to pad four characters. After the padding, the string becomes ****base.

1
2
3
4
5

SELECT lpad('base',8,'*');
   lpad
----------
 ****base
(1 row)

Set original string length (database) to 8 and the target length to 6, and truncate the string database from the right to a string of 6 characters. databa is returned.

1
2
3
4
5

SELECT lpad('database',6,'data');
  lpad
--------
 databa
(1 row)

concat(str1,str2)

Description: Connects str1 and str2 and returns the string.

• In the ORA- or TD-compatible mode, a combination of all the non-null strings is returned.
• In the MySQL-compatible mode, NULL is returned if an input string is NULL.

Return type: varchar

Example:

1
2
3
4
5

SELECT concat('Hello', ' World!');
    concat    
--------------
 Hello World!
(1 row)

chr(integer)

Description: Converts an integer to the corresponding ASCII character.

Return type: varchar

Example:

1
2
3
4
5

SELECT chr(65);
 chr
-----
 A
(1 row)

regexp_substr(source_char, pattern)

Description: Extracts substrings from a regular expression.

Return type: varchar

Example:

1
2
3
4
5

SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR";
  REGEXPR_SUBSTR   
-------------------
 , Redwood Shores,
(1 row)

regexp_replace(string, pattern, replacement [,flags ])

Description: Replaces substring matching POSIX regular expression. The source string is returned unchanged if there is no match to the pattern. If there is a match, the source string is returned with the replacement string substituted for the matching substring.

The replacement string can contain \n, where n is 1 through 9, to indicate that the source substring matching the nth parenthesized sub-expression of the pattern should be inserted, and it can contain \& to indicate that the substring matching the entire pattern should be inserted.

The optional flags argument contains zero or multiple single-letter flags that change function behavior. The following table lists the options of the flags argument.

Return type: varchar

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT regexp_replace('Thomas', '.[mN]a.', 'M');
 regexp_replace
----------------
 ThM
(1 row)
SELECT regexp_replace('foobarbaz','b(..)', E'X\\1Y', 'g') AS RESULT;                                                    
   result    
-------------
 fooXarYXazY
(1 row)

concat_ws(sep text, str"any" [, str"any" [, ...] ])

Description: Uses the first parameter as the separator, which is associated with all following parameters.

Return type: text

Example:

1
2
3
4
5

SELECT concat_ws(',', 'ABCDE', 2, NULL, 22);
 concat_ws
------------
 ABCDE,2,22
(1 row)

convert(string bytea, src_encoding name, dest_encoding name)

Description: Converts the bytea string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: bytea

Example:

1
2
3
4
5

SELECT convert('text_in_utf8', 'UTF8', 'GBK');
          convert        
----------------------------
 \x746578745f696e5f75746638
(1 row)

If the rule for converting between source to target encoding (for example, GBK and LATIN1) does not exist, the string is returned without conversion. See the pg_conversion system catalog for details.

Example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

show server_encoding; server_encoding ----------------- LATIN1 (1 row) SELECT convert_from('some text', 'GBK'); convert_from -------------- some text (1 row) db_latin1=# SELECT convert_to('some text', 'GBK'); convert_to ---------------------- \x736f6d652074657874 (1 row) db_latin1=# SELECT convert('some text', 'GBK', 'LATIN1'); convert ---------------------- \x736f6d652074657874 (1 row)

convert_from(string bytea, src_encoding name)

Description: Converts the long bytea using the coding mode of the database.

src_encoding specifies the source code encoding. The string must be valid in this encoding.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT convert_from('text_in_utf8', 'UTF8');
 convert_from
--------------
 text_in_utf8
(1 row)
SELECT convert_from('\x6461746162617365','gbk');
 convert_from
--------------
 database
(1 row)

convert_to(string text, dest_encoding name)

Description: Converts the given string into one whose encoding format is dest_encoding.

Return type: bytea

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT convert_to('some text', 'UTF8');
      convert_to
----------------------
 \x736f6d652074657874
(1 row)
SELECT convert_to('database', 'gbk');
     convert_to
--------------------
 \x6461746162617365
(1 row)

string [NOT] LIKE pattern [ESCAPE escape-character]

Description: Pattern matching function

If the pattern does not include a percentage sign (%) or an underscore (_), this mode represents itself only. In this case, the behavior of LIKE is the same as the equal operator. The underscore (_) in the pattern matches any single character while one percentage sign (%) matches no or multiple characters.

To match with underscores (_) or percent signs (%), corresponding characters in pattern must lead escape characters. The default escape character is a backward slash (\) and can be specified using the ESCAPE clause. To match with escape characters, enter two escape characters.

Return type: boolean

Example:

1
2
3
4
5

SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT;
 result
--------
 t
(1 row)

1
2
3
4
5

SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT;
 result
--------
 f
(1 row)

1
2
3
4
5

SELECT 'AA@_BBCC' LIKE '%A@_B%' AS RESULT;
 result
--------
 t
(1 row)

REGEXP_LIKE(source_string, pattern [, match_parameter])

Description: Performs a regular expression matching.

source_string indicates the source string and pattern indicates the matching pattern of the regular expression. match_parameter indicates the matching items and the values are as follows:

• "i": case-insensitive
• "c": case-sensitive
• "n": allowing the metacharacter "." in a regular expression to be matched with a linefeed.
• "m": allows source_string to be regarded as multiple rows.

If match_parameter is ignored, case-sensitive is enabled by default, "." is not matched with a linefeed, and source_string is regarded as a single row.

Return type: boolean

Example:

1
2
3
4
5

SELECT regexp_like('ABC', '[A-Z]');
 regexp_like
-------------
 t
(1 row)

1
2
3
4
5

SELECT regexp_like('ABC', '[D-Z]');
 regexp_like
-------------
 f
(1 row)

1
2
3
4
5

SELECT regexp_like('abc', '[A-Z]','i');
 regexp_like
-------------
 t
(1 row)

1
2
3
4
5

SELECT regexp_like('abc', '[A-Z]');
 regexp_like
-------------
 f
(1 row)

format(formatstr text [, str"any" [, ...] ])

Description: Formats a string.

Return type: text

Example:

1
2
3
4
5

SELECT format('Hello %s, %1$s', 'World');
       format       
--------------------
 Hello World, World
(1 row)

md5(string)

Description: Encrypts a string using MD5 and returns a hexadecimal number.

MD5 is insecure and is not recommended.

Return type: text

Example:

1
2
3
4
5

SELECT md5('data');
               md5
----------------------------------
 8d777f385d3dfec8815d20f7496026dc
(1 row)

decode(string text, format text)

Description: Decodes binary data from textual representation.

Return type: bytea

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT decode('ZGF0YWJhc2U=', 'base64');
    decode    
--------------
 \x6461746162617365
(1 row)

SELECT convert_from('\x6461746162617365','utf-8');
 convert_from
--------------
 database
(1 row)

encode(data bytea, format text)

Description: Encodes binary data into a textual representation.

Return type: text

Example:

1
2
3
4
5

SELECT encode('database', 'base64');
  encode  
----------
 ZGF0YWJhc2U=
(1 row)

CONV(n, fromBase, toBase)

Description: Converts the given value or string into value of a specific number system and outputs the result as a string. If the parameter contains NULL, NULL will be returned. The output value range is [-36, -2] & [2, 36].

Return type: text

Example:

1
2
3
4
5

SELECT CONV(-1, 10, 16) as result;
      result
------------------
 FFFFFFFFFFFFFFFF
(1 row)

HEX(n)

Description: Returns the hexadecimal string of n. n can be an integer or a string. If the parameter contains NULL, NULL will be returned.

Return type: text

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT HEX(255) as result;
 result
--------
   FF
(1 row)
SELECT HEX('abc') as result;
 result
--------
 616263
(1 row)

UNHEX(n)

Description: Performs the reverse operation of HEX(n). n can be an int or a string. Each pair of hexadecimal digits in the parameter is regarded as a number and converted into the integer representation of the hexadecimal value. If the parameter contains NULL, NULL will be returned. This function is supported by version 8.2.0 or later clusters.

Return type: bytea

Example:

1
2
3
4
5

SELECT UNHEX('abc') as result;
 result
--------
 \x0abc
(1 row)

SPACE(n int)

Description: Returns a string consisting of n spaces. If the parameter contains NULL, NULL will be returned.

Return type: text

Example:

1
2
3
4
5

SELECT SPACE(2) as result;
 result
--------
   
(1 row)

STRCMP(text, text)

Description: Compares two strings. If all strings are the same, 0 is returned. If the first string is smaller than the second string, -1 is returned. In other cases, 1 is returned. If the parameter contains NULL, NULL will be returned.

Return type: text

Example:

1
2
3
4
5

SELECT STRCMP('AA', 'AA'), STRCMP('AA', 'AB'), STRCMP('AA', 'A');
STRCMP  |  STRCMP  |  STRCMP
------------------------------
    0   |    -1    |     1
(1 row)

BIN(n bigint)

Description: Converts the bigint type from decimal to binary and returns the result as a string. If the parameter contains NULL, NULL will be returned.

Return type: text

1
2
3
4
5

SELECT BIN(16) as result;
 result
--------
 10000
(1 row)