String Functions and Operators

Note

In the string functions, if the value of oracle_style_empty_string parameter is yes, CUBRID does not separate an empty string and NULL; according to each function, CUBRID regards all of them as NULL or an empty string. For the detail description, see oracle_style_empty_string.

Concatenation Operator

A concatenation operator gets a character string or bit string data type as an operand and returns a concatenated string. The plus sign (+) and double pipe symbol (||) are provided as concatenation operators for character string data. If NULL is specified as an operand, a NULL value is returned.

If pipes_as_concat that is a parameter related to SQL statement is set to no (default value: yes), a double pipe (||) symbol is interpreted as an OR operator. If plus_as_concat is set to no (default value: yes), a plus (+) symbol is interpreted as a plus (+) operator. In such case, It is recommended to concatenate strings or bit strings, by using the CONCAT function.

<concat_operand1> +  <concat_operand1>
<concat_operand2> || <concat_operand2>

    <concat_operand1> ::=
        bit string |
        NULL

    <concat_operand2> ::=
        bit string |
        character string
        NULL

• <concat_operand1>: Left string after concatenation. String or bit string can be specified.

• <concat_operand2>: Right string after concatenation. String or bit string can be specified.

SELECT 'CUBRID' || ',' + '2008';

 'CUBRID'||','+'2008'
======================
  'CUBRID,2008'

SELECT 'cubrid' || ',' || B'0010' ||B'0000' ||B'0000' ||B'1000';

 'cubrid'||','||B'0010'||B'0000'||B'0000'||B'1000'
======================
  'cubrid,2008'

SELECT ((EXTRACT(YEAR FROM SYS_TIMESTAMP))||(EXTRACT(MONTH FROM SYS_TIMESTAMP)));

 (( extract(year  from  SYS_TIMESTAMP ))||( extract(month  from  SYS_TIMESTAMP )))
======================
  '200812'

SELECT 'CUBRID' || ',' + NULL;

 'CUBRID'||','+null
======================
  NULL

ASCII

ASCII(str)

The ASCII function returns the ASCII code of the most left character in numeric value. If an input string is NULL, NULL is returned. This ASCII function supports single-byte character sets only. If a numeric value is entered, it is converted into character string and then the ASCII code of the most left character is returned.

Parameters:

str – Input string

Return type:

STRING

SELECT ASCII('5');

53

SELECT ASCII('ab');

97

BIN

BIN(n)

The BIN function converts a BIGINT type number into binary string. If an input string is NULL, NULL is returned. When you input the string which cannot be transformed into BIGINT, it returns an error if the value of return_null_on_function_errors in cubrid.conf is no(the default), or returns NULL if it is yes.

Parameters:

n – A BIGINT type number

Return type:

STRING

SELECT BIN(12);

'1100'

BIT_LENGTH

BIT_LENGTH(string)

The BIT_LENGTH function returns the length (bits) of a character string or bit string as an integer value. The return value of the BIT_LENGTH function may depend on the character set, because for the character string, the number of bytes taken up by a single character is different depending on the character set of the data input environment (e.g., UTF-8 Korean characters: one Korean character is 3*8 bits). For details about character sets supported by CUBRID, see Character Strings. When you input the invalid value, it returns an error if the value of return_null_on_function_errors in cubrid.conf is no(the default), or returns NULL if it is yes.

Parameters:

string – Specifies the character string or bit string whose number of bits is to be calculated. If this value is NULL, NULL is returned.

Return type:

INT

SELECT BIT_LENGTH('');

   bit_length('')
=================
                0

SELECT BIT_LENGTH('CUBRID');

   bit_length('CUBRID')
=======================
                     48

-- UTF-8 Korean character
SELECT BIT_LENGTH('큐브리드');

     bit_length('큐브리드')
=========================
                       96

SELECT BIT_LENGTH(B'010101010');

   bit_length(B'010101010')
===========================
                          9

CREATE TABLE bit_length_tbl (char_1 CHAR, char_2 CHAR(5), varchar_1 VARCHAR, bit_var_1 BIT VARYING);
INSERT INTO bit_length_tbl VALUES('', '', '', B''); --Length of empty string
INSERT INTO bit_length_tbl VALUES('a', 'a', 'a', B'010101010'); --English character
INSERT INTO bit_length_tbl VALUES(NULL, '큐', '큐', B'010101010'); --UTF-8 Korean character and NULL
INSERT INTO bit_length_tbl VALUES(' ', ' 큐', ' 큐', B'010101010'); --UTF-8 Korean character and space

SELECT BIT_LENGTH(char_1), BIT_LENGTH(char_2), BIT_LENGTH(varchar_1), BIT_LENGTH(bit_var_1) FROM bit_length_tbl;

bit_length(char_1)  bit_length(char_2)      bit_length(varchar_1)   bit_length(bit_var_1)
================================================================================
8                   40                       0                       0
8                   40                       8                       9
NULL                56                      24                       9
8                   40                      32                       9

CHAR_LENGTH, CHARACTER_LENGTH, LENGTHB, LENGTH

CHAR_LENGTH(string)

CHARACTER_LENGTH(string)

LENGTHB(string)

LENGTH(string)

CHAR_LENGTH, CHARACTER_LENGTH, LENGTHB, and LENGTH are used interchangeably. The number of characters is returned as an integer. For details on character set supported by CUBRID, see An Overview of Globalization.

Parameters:

string – Specifies the string whose length will be calculated according to the number of characters. If the character string is NULL, NULL is returned.

Return type:

INT

Note

• In versions lower than CUBRID 9.0, the multibyte string returns the number of bytes in the string. Therefore, the length of one character is calculated as 2- or 3-bytes according to the charset.

• The length of each space character that is included in a character string is one byte.

• The length of empty quotes (”) to represent a space character is 0. Note that in a CHAR (n) type, the length of a space character is n, and it is specified as 1 if n is omitted.

--character set is UTF-8 for Korean characters
SELECT LENGTH('');

char length('')
==================
                 0

SELECT LENGTH('CUBRID');

char length('CUBRID')
==================
                 6

SELECT LENGTH('큐브리드');

char length('큐브리드')
==================
                 4

CREATE TABLE length_tbl (char_1 CHAR, char_2 CHAR(5), varchar_1 VARCHAR, varchar_2 VARCHAR);
INSERT INTO length_tbl VALUES('', '', '', ''); --Length of empty string
INSERT INTO length_tbl VALUES('a', 'a', 'a', 'a'); --English character
INSERT INTO length_tbl VALUES(NULL, '큐', '큐', '큐'); --Korean character and NULL
INSERT INTO length_tbl VALUES(' ', ' 큐', ' 큐', ' 큐'); --Korean character and space

SELECT LENGTH(char_1), LENGTH(char_2), LENGTH(varchar_1), LENGTH(varchar_2) FROM length_tbl;

char_length(char_1) char_length(char_2) char_length(varchar_1) char_length(varchar_2)
================================================================================
1                     5                        0             0
1                     5                        1             1
NULL                  5                        1             1
1                     5                        2             2

CHR

CHR(number_operand [USING charset_name])

The CHR function returns a character that corresponds to the return value of the expression specified as an argument. When you input the code value within invalid ranges, it returns an error if the value of return_null_on_function_errors in cubrid.conf is no(the default), or returns NULL if it is yes.

Parameters:

• number_operand – Specifies an expression that returns a numeric value.

• charset_name – Characterset name. It supports utf8 and iso88591.

Return type:

STRING

SELECT CHR(68) || CHR(68-2);

   chr(68)|| chr(68-2)
======================
  'DB'

If you want to get a multibyte character with the CHR function, input a number with the valid range of the charset.

SELECT CHR(14909886 USING utf8);
-- Below query's result is the same as above.
SET NAMES utf8;
SELECT CHR(14909886);

   chr(14909886 using utf8)
======================
  'ま'

If you want to get the hexadecimal string from a character, use HEX function.

SET NAMES utf8;
SELECT HEX('ま');

   hex(_utf8'ま')
======================
  'E381BE'

If you want to get the decimal string from a hexadecimal string, use CONV function.

SET NAMES utf8;
SELECT CONV('E381BE',16,10);

   conv(_utf8'E381BE', 16, 10)
======================
  '14909886'

CONCAT

CONCAT(string1, string2 [,string3 [, ... [, stringN]...]])

The CONCAT function has at least one argument specified for it and returns a string as a result of concatenating all argument values. The number of parameters that can be specified is unlimited. Automatic type casting takes place if a non-string type is specified as the argument. If any of the arguments is specified as NULL, NULL is returned.

If you want to insert separators between strings specified as arguments for concatenation, use the CONCAT_WS() Function.

Parameters:

strings – character string

Return type:

STRING

SELECT CONCAT('CUBRID', '2008' , 'R3.0');

   concat('CUBRID', '2008', 'R3.0')
======================
'CUBRID2008R3.0'

--it returns null when null is specified for one of parameters
SELECT CONCAT('CUBRID', '2008' , 'R3.0', NULL);

   concat('CUBRID', '2008', 'R3.0', null)
======================
  NULL

--it converts number types and then returns concatenated strings
SELECT CONCAT(2008, 3.0);

   concat(2008, 3.0)
======================
  '20083.0'

CONCAT_WS

CONCAT_WS(string1, string2 [,string3 [, ... [, stringN]...]])

The CONCAT_WS function has at least two arguments specified for it. The function uses the first argument value as the separator and returns the result.

Parameters:

strings – character string

Return type:

STRING

SELECT CONCAT_WS(' ', 'CUBRID', '2008' , 'R3.0');

concat_ws(' ', 'CUBRID', '2008', 'R3.0')
======================
  'CUBRID 2008 R3.0'

--it returns strings even if null is specified for one of parameters
SELECT CONCAT_WS(' ', 'CUBRID', '2008', NULL, 'R3.0');

concat_ws(' ', 'CUBRID', '2008', null, 'R3.0')
======================
  'CUBRID 2008 R3.0'

--it converts number types and then returns concatenated strings with separator
SELECT CONCAT_WS(' ',2008, 3.0);

concat_ws(' ', 2008, 3.0)
======================
  '2008 3.0'

ELT

ELT(N, string1, string2, ...)

If N is 1, the ELT function returns string1 and if N is 2, it returns string2. The return value is a VARCHAR type. You can add conditional expressions as needed.

The maximum byte length of the character string is 33,554,432 and if this length is exceeded, NULL will be returned.

If N is 0 or a negative number, an empty string will be returned. If N is greater than the number of this input character string, NULL will be returned as it is out of range. If N is a type that cannot be converted to an integer, an error will be returned.

Parameters:

• N – A position of a string to return among the list of strings

• strings – The list of strings

Return type:

STRING

SELECT ELT(3,'string1','string2','string3');

  elt(3, 'string1', 'string2', 'string3')
======================
  'string3'

SELECT ELT('3','1/1/1','23:00:00','2001-03-04');

  elt('3', '1/1/1', '23:00:00', '2001-03-04')
======================
  '2001-03-04'

SELECT ELT(-1, 'string1','string2','string3');

  elt(-1, 'string1','string2','string3')
======================
  NULL

SELECT ELT(4,'string1','string2','string3');

  elt(4, 'string1', 'string2', 'string3')
======================
  NULL

SELECT ELT(3.2,'string1','string2','string3');

  elt(3.2, 'string1', 'string2', 'string3')
======================
  'string3'

SELECT ELT('a','string1','string2','string3');

ERROR: Cannot coerce 'a' to type bigint.

FIELD

FIELD(search_string, string1 [,string2 [, ... [, stringN]...]])

The FIELD function returns the location index value (position) of a string of string1, string2. The function returns 0 if it does not have a parameter value which is the same as search_string. It returns 0 if search_string is NULL because it cannot perform the comparison operation with the other arguments.

If all arguments specified for FIELD function are of string type, string comparison operation is performed: if all of them are of number type, numeric comparison operation is performed. If the type of one argument is different from that of another, a comparison operation is performed by casting each argument to the type of the first argument. If type casting fails during the comparison operation with each argument, the function considers the result of the comparison operation as FALSE and resumes the other operations.

Parameters:

• search_string – A string pattern to search

• strings – The list of strings to be searched

Return type:

INT

SELECT FIELD('abc', 'a', 'ab', 'abc', 'abcd', 'abcde');

   field('abc', 'a', 'ab', 'abc', 'abcd', 'abcde')
==================================================
                                                 3

--it returns 0 when no same string is found in the list
SELECT FIELD('abc', 'a', 'ab', NULL);

   field('abc', 'a', 'ab', null)
================================
                               0

--it returns 0 when null is specified in the first parameter
SELECT FIELD(NULL, 'a', 'ab', NULL);

   field(null, 'a', 'ab', null)
===============================
                              0

SELECT FIELD('123', 1, 12, 123.0, 1234, 12345);

   field('123', 1, 12, 123.0, 1234, 12345)
==========================================
                                         0

SELECT FIELD(123, 1, 12, '123.0', 1234, 12345);

   field(123, 1, 12, '123.0', 1234, 12345)
==============================================
                                             3

FIND_IN_SET

FIND_IN_SET(str, strlist)

The FIND_IN_SET function looks for the string str in the string list strlist and returns a position of str if it exists. A string list is a string composed of substrings separated by a comma (,). If str is not in strlist or strlist is an empty string, 0 is returned. If either argument is NULL, NULL is returned. This function does not work properly if str contains a comma (,).

Parameters:

• str – A string to be searched

• strlist – A group of strings separated by a comma

Return type:

INT

SELECT FIND_IN_SET('b','a,b,c,d');

2

FROM_BASE64

FROM_BASE64(str)

FROM_BASE64 function returns the the decoded result as binary string from the input string encoded as base-64 rule, which is used in TO_BASE64 function. If the input value is NULL, it returns NULL. When you input the invalid base-64 string, it returns an error if the value of **return_null_on_function_errors in cubrid.conf is no(the default); NULL if this value is yes. See TO_BASE64() for more details on base-64 encoding rules.

Parameters:

str – Input string

Return type:

STRING

SELECT TO_BASE64('abcd'), FROM_BASE64(TO_BASE64('abcd'));

   to_base64('abcd') from_base64( to_base64('abcd'))
============================================
  'YWJjZA==' 'abcd'

See also

TO_BASE64()

INSERT

INSERT(str, pos, len, string)

The INSERT function inserts a partial character string as long as the length from the specific location of the input character string. The return value is a VARCHAR type. The maximum length of the character string is 33,554,432 and if this length is exceeded, NULL will be returned.

Parameters:

• str – Input character string

• pos – str location. Starts from 1. If pos is smaller than 1 or greater than the length of string + 1, the string will not be inserted and the str will be returned instead.

• len – Length of string to insert pos of str. If len exceeds the length of the partial character string, insert as many values as string in the pos of the str . If len is a negative number, str will be the end of the character string.

• string – Partial character string to insert to str

Return type:

STRING

SELECT INSERT('cubrid',2,2,'dbsql');

  insert('cubrid', 2, 2, 'dbsql')
======================
  'cdbsqlrid'

SELECT INSERT('cubrid',0,3,'db');

  insert('cubrid', 0, 3, 'db')
======================
  'cubrid'

SELECT INSERT('cubrid',-3,3,'db');

  insert('cubrid', -3, 3, 'db')
======================
  'cubrid'

SELECT INSERT('cubrid',3,100,'db');

  insert('cubrid', 3, 100, 'db')
======================
  'cudb'

SELECT INSERT('cubrid',7,100,'db');

  insert('cubrid', 7, 100, 'db')
======================
  'cubriddb'

SELECT INSERT('cubrid',3,-1,'db');

  insert('cubrid', 3, -1, 'db')
======================
  'cudb'

INSTR

INSTR(string, substring[, position])

The INSTR function, similarly to the POSITION, returns the position of a substring within string; the position. For the INSTR function, you can specify the starting position of the search for substring to make it possible to search for duplicate substring.

Parameters:

• string – Specifies the input character string.

• substring – Specifies the character string whose position is to be returned.

• position – Optional. Represents the position of a string where the search begins in character unit. If omitted, the default value 1 is applied. The first position of the string is specified as 1. If the value is negative, the system counts backward from the end of the string.

Return type:

INT

Note

In the earlier versions of CUBRID 9.0, position value is returned in byte unit, not character unit. When a multi-byte character set is used, the number of bytes representing one character is different; so the return value may not the same.

--character set is UTF-8 for Korean characters
--it returns position of the first 'b'
SELECT INSTR ('12345abcdeabcde','b');

   instr('12345abcdeabcde', 'b', 1)
===================================
                                  7

-- it returns position of the first '나' on UTF-8 Korean charset
SELECT INSTR ('12345가나다라마가나다라마', '나' );

   instr('12345가나다라마가나다라마', '나', 1)
=================================
                                7

-- it returns position of the second '나' on UTF-8 Korean charset
SELECT INSTR ('12345가나다라마가나다라마', '나', 11 );

   instr('12345가나다라마가나다라마', '나', 11)
=================================
                               12

--it returns position of the 'b' searching from the 8th position
SELECT INSTR ('12345abcdeabcde','b', 8);

   instr('12345abcdeabcde', 'b', 8)
===================================
                                 12

--it returns position of the 'b' searching backwardly from the end
SELECT INSTR ('12345abcdeabcde','b', -1);

   instr('12345abcdeabcde', 'b', -1)
====================================
                                  12

--it returns position of the 'b' searching backwardly from a specified position
SELECT INSTR ('12345abcdeabcde','b', -8);

   instr('12345abcdeabcde', 'b', -8)
====================================
                                   7

LCASE, LOWER

LCASE(string)

LOWER(string)

The functions LCASE and LOWER are used interchangeably. They convert uppercase characters included in string to lowercase characters.

Parameters:

string – Specifies the string in which uppercase characters are to be converted to lowercase. If the value is NULL, NULL is returned.

Return type:

STRING

SELECT LOWER('');

  lower('')
======================
  ''

SELECT LOWER(NULL);

  lower(null)
======================
  NULL

SELECT LOWER('Cubrid');

  lower('Cubrid')
======================
  'cubrid'

Note that the LOWER function may not work properly by specified collation. For example, when you try to change character Ă used in Romanian as lower character, this function works as follows by collation.

If collation is utf8_bin, this character is not changed.

SET NAMES utf8 COLLATE utf8_bin;
SELECT LOWER('Ă');

   lower(_utf8'Ă')
======================
  'Ă'

If collation is utf8_ro_RO, ‘Ă’ can be changed.

SET NAMES utf8 COLLATE utf8_ro_cs;
SELECT LOWER('Ă');

   lower(_utf8'Ă' COLLATE utf8_ro_cs)
======================
  'ă'

For supporting collations in CUBRID, see CUBRID Collation.

LEFT

LEFT(string, length)

The LEFT function returns a length number of characters from the leftmost string. If any of the arguments is NULL, NULL is returned. If a value greater than the length of the string or a negative number is specified for a length, the entire string is returned. To extract a length number of characters from the rightmost string, use the RIGHT().

Parameters:

• string – Input string

• length – The length of a string to be returned

Return type:

STRING

SELECT LEFT('CUBRID', 3);

 left('CUBRID', 3)
======================
  'CUB'

SELECT LEFT('CUBRID', 10);

  left('CUBRID', 10)
======================
  'CUBRID'

LOCATE

LOCATE(substring, string[, position])

The LOCATE function returns the location index value of a substring within a character string. The third argument position can be omitted. If this argument is specified, the function searches for substring from the given position and returns the location index value of the first occurrence. If the substring cannot be found within the string, 0 is returned. The LOCATE function behaves like the POSITION(), but you cannot use LOCATE for bit strings.

Parameters:

• substring – A string pattern to search

• string – A whole string to be searched

• position – Starting position of a whole string to be searched

Return type:

INT

--it returns 1 when substring is empty space
SELECT LOCATE ('', '12345abcdeabcde');

 locate('', '12345abcdeabcde')
===============================
                             1

--it returns position of the first 'abc'
SELECT LOCATE ('abc', '12345abcdeabcde');

 locate('abc', '12345abcdeabcde')
================================
                               6

--it returns position of the second 'abc'
SELECT LOCATE ('abc', '12345abcdeabcde', 8);

 locate('abc', '12345abcdeabcde', 8)
======================================
                                  11

--it returns 0 when no substring found in the string
SELECT LOCATE ('ABC', '12345abcdeabcde');

 locate('ABC', '12345abcdeabcde')
=================================
                                0

LPAD

LPAD(char1, n[, char2])

The LPAD function pads the left side of a string until the string length reaches the specified value.

Parameters:

• char1 – Specifies the string to pad characters to. If n is smaller than the length of char1, padding is not performed, and char1 is truncated to length n and then returned. If the value is NULL, NULL is returned.

• n – Specifies the total length of char1 in bytes. If the value is NULL, NULL is returned.

• char2 – Specifies the string to pad to the left until the length of char1 reaches n. If it is not specified, empty characters (’ ‘) are used as a default. If the value is NULL, NULL is returned.

Return type:

STRING

Note

In versions lower than CUBRID 9.0, a single character is processed as 2 or 3 bytes in a multi-byte character set environment. If n is truncated up to the first byte representing a character according to a value of char1, the last byte is removed and a space character (1 byte) is added to the left because the last character cannot be represented normally. When the value is NULL, NULL is returned as its result.

--character set is UTF-8 for Korean characters

--it returns only 3 characters if not enough length is specified
SELECT LPAD ('CUBRID', 3, '?');

  lpad('CUBRID', 3, '?')
======================
  'CUB'

SELECT LPAD ('큐브리드', 3, '?');

 lpad('큐브리드', 3, '?')
======================
  '큐브리'

--padding spaces on the left till char_length is 10
SELECT LPAD ('CUBRID', 10);

 lpad('CUBRID', 10)
======================
  '    CUBRID'

--padding specific characters on the left till char_length is 10
SELECT LPAD ('CUBRID', 10, '?');

 lpad('CUBRID', 10, '?')
======================
  '????CUBRID'

--padding specific characters on the left till char_length is 10
SELECT LPAD ('큐브리드', 10, '?');

 lpad('큐브리드', 10, '?')
======================
  '??????큐브리드'

--padding 4 characters on the left
SELECT LPAD ('큐브리드', LENGTH('큐브리드')+4, '?');

 lpad('큐브리드',  char_length('큐브리드')+4, '?')
======================
  '????큐브리드'

LTRIM

LTRIM(string[, trim_string])

The LTRIM function removes all specified characters from the left-hand side of a string.

Parameters:

• string – Enters a string or string-type column to trim. If this value is NULL, NULL is returned.

• trim_string – You can specify a specific string to be removed in the left side of string. If it is not specified, empty characters (’ ‘) is automatically specified so that the empty characters in the left side are removed.

Return type:

STRING

--trimming spaces on the left
SELECT LTRIM ('     Olympic     ');

  ltrim('     Olympic     ')
======================
  'Olympic     '

--If NULL is specified, it returns NULL
SELECT LTRIM ('iiiiiOlympiciiiii', NULL);

  ltrim('iiiiiOlympiciiiii', null)
======================
  NULL

-- trimming specific strings on the left
SELECT LTRIM ('iiiiiOlympiciiiii', 'i');

  ltrim('iiiiiOlympiciiiii', 'i')
======================
  'Olympiciiiii'

MID

MID(string, position, substring_length)

The MID function extracts a string with the length of substring_length from a position within the string and then returns it. If a negative number is specified as a position value, the position is calculated in a reverse direction from the end of the string. substring_length cannot be omitted. If a negative value is specified, the function considers this as 0 and returns an empty string.

The MID function is working like the SUBSTR(), but there are differences in that it cannot be used for bit strings, that the substring_length argument must be specified, and that it returns an empty string if a negative number is specified for substring_length.

Parameters:

• string – Specifies an input character string. If this value is NULL, NULL is returned.

• position – Specifies the starting position from which the string is to be extracted. The position of the first character is 1. It is considered to be 1 even if it is specified as 0. If the input value is NULL, NULL is returned.

• substring_length – Specifies the length of the string to be extracted. If 0 or a negative number is specified, an empty string is returned; if NULL is specified, NULL is returned.

Return type:

STRING

CREATE TABLE mid_tbl(a VARCHAR);
INSERT INTO mid_tbl VALUES('12345abcdeabcde');

--it returns empty string when substring_length is 0
SELECT MID(a, 6, 0), SUBSTR(a, 6, 0), SUBSTRING(a, 6, 0) FROM mid_tbl;

  mid(a, 6, 0)          substr(a, 6, 0)       substring(a from 6 for 0)
==================================================================
  ''                    ''                    ''

--it returns 4-length substrings counting from the 6th position
SELECT MID(a, 6, 4), SUBSTR(a, 6, 4), SUBSTRING(a, 6, 4) FROM mid_tbl;

  mid(a, 6, 4)          substr(a, 6, 4)       substring(a from 6 for 4)
==================================================================
  'abcd'                'abcd'                'abcd'

--it returns an empty string when substring_length < 0
SELECT MID(a, 6, -4), SUBSTR(a, 6, -4), SUBSTRING(a, 6, -4) FROM mid_tbl;

  mid(a, 6, -4)         substr(a, 6, -4)      substring(a from 6 for -4)
==================================================================
  ''                    NULL                  'abcdeabcde'

--it returns 4-length substrings at 6th position counting backward from the end
SELECT MID(a, -6, 4), SUBSTR(a, -6, 4), SUBSTRING(a, -6, 4) FROM mid_tbl;

  mid(a, -6, 4)         substr(a, -6, 4)      substring(a from -6 for 4)
==================================================================
  'eabc'                'eabc'                '1234'

OCTET_LENGTH

OCTET_LENGTH(string)

The OCTET_LENGTH function returns the length (byte) of a character string or bit string as an integer. Therefore, it returns 1 (byte) if the length of the bit string is 8 bits, but 2 (bytes) if the length is 9 bits.

Parameters:

string – Specifies the character or bit string whose length is to be returned in bytes. If the value is NULL, NULL is returned.

Return type:

INT

--character set is UTF-8 for Korean characters

SELECT OCTET_LENGTH('');

 octet_length('')
==================
                 0

SELECT OCTET_LENGTH('CUBRID');

 octet_length('CUBRID')
==================
                 6

SELECT OCTET_LENGTH('큐브리드');

 octet_length('큐브리드')
==================
                 12

SELECT OCTET_LENGTH(B'010101010');

 octet_length(B'010101010')
==================
                 2

CREATE TABLE octet_length_tbl (char_1 CHAR, char_2 CHAR(5), varchar_1 VARCHAR, bit_var_1 BIT VARYING);
INSERT INTO octet_length_tbl VALUES('', '', '', B''); --Length of empty string
INSERT INTO octet_length_tbl VALUES('a', 'a', 'a', B'010101010'); --English character
INSERT INTO octet_length_tbl VALUES(NULL, '큐', '큐', B'010101010'); --Korean character and NULL
INSERT INTO octet_length_tbl VALUES(' ', ' 큐', ' 큐', B'010101010'); --Korean character and space

SELECT OCTET_LENGTH(char_1), OCTET_LENGTH(char_2), OCTET_LENGTH(varchar_1), OCTET_LENGTH(bit_var_1) FROM octet_length_tbl;

octet_length(char_1) octet_length(char_2) octet_length(varchar_1) octet_length(bit_var_1)
================================================================================
1                      5                         0                       0
1                      5                         1                       2
NULL                   7                         3                       2
1                      7                         4                       2

POSITION

POSITION(substring IN string)

The POSITION function returns the position of a character string corresponding to substring within a character string corresponding to string.

An expression that returns a character string or a bit string can be specified as an argument of this function. The return value is an integer greater than or equal to 0. This function returns the position value in character unit for a character string, and in bits for a bit string.

The POSITION function is occasionally used in combination with other functions. For example, if you want to extract a certain string from another string, you can use the result of the POSITION function as an input to the SUBSTRING function.

Note

The location is returned in the unit of byte, not the character, in version lower than CUBRID 9.0. The multi-byte charset uses different numbers of bytes to express one character, so the result value may differ.

Parameters:

substring – Specifies the character string whose position is to be returned. If the value is an empty character, 1 is returned. If the value is NULL, NULL is returned.

Return type:

INT

--character set is UTF-8 for Korean characters

--it returns 1 when substring is empty space
SELECT POSITION ('' IN '12345abcdeabcde');

  position('' in '12345abcdeabcde')
===============================
                              1

--it returns position of the first 'b'
SELECT POSITION ('b' IN '12345abcdeabcde');

  position('b' in '12345abcdeabcde')
================================
                               7

-- it returns position of the first '나'
SELECT POSITION ('나' IN '12345가나다라마가나다라마');

  position('나' in '12345가나다라마가나다라마')
=================================
                                7

--it returns 0 when no substring found in the string
SELECT POSITION ('f' IN '12345abcdeabcde');

  position('f' in '12345abcdeabcde')
=================================
                                0

SELECT POSITION (B'1' IN B'000011110000');

  position(B'1' in B'000011110000')
=================================
                                5

REPEAT

REPEAT(string, count)

The REPEAT function returns the character string with a length equal to the number of repeated input character strings. The return value is a VARCHAR type. The maximum length of the character string is 33,554,432 and if it this length is exceeded, NULL will be returned. If one of the parameters is NULL, NULL will be returned.

Parameters:

• substring – Character string

• count – Repeat count. If you enter 0 or a negative number, an empty string will be returned and if you enter a non-numeric data type, an error will be returned.

Return type:

STRING

SELECT REPEAT('cubrid',3);

   repeat('cubrid', 3)
======================
  'cubridcubridcubrid'

SELECT REPEAT('cubrid',32000000);

   repeat('cubrid', 32000000)
======================
  NULL

SELECT REPEAT('cubrid',-1);

   repeat('cubrid', -1)
======================
  ''

SELECT REPEAT('cubrid','a');

ERROR: Cannot coerce 'a' to type integer.

REPLACE

REPLACE(string, search_string[, replacement_string])

The REPLACE function searches for a character string, search_string, within a given character string, string, and replaces it with a character string, replacement_string. If the string to be replaced, replacement_string is omitted, all search_strings retrieved from string are removed. If NULL is specified as an argument, NULL is returned.

Parameters:

• string – Specifies the original string. If the value is NULL, NULL is returned.

• search_string – Specifies the string to be searched. If the value is NULL, NULL is returned

• replacement_string – Specifies the string to replace the search_string. If this value is omitted, string is returned with the search_string removed. If the value is NULL, NULL is returned.

Return type:

STRING

--it returns NULL when an argument is specified with NULL value
SELECT REPLACE('12345abcdeabcde','abcde',NULL);

replace('12345abcdeabcde', 'abcde', null)
======================
  NULL

--not only the first substring but all substrings into 'ABCDE' are replaced
SELECT REPLACE('12345abcdeabcde','abcde','ABCDE');

replace('12345abcdeabcde', 'abcde', 'ABCDE')
======================
  '12345ABCDEABCDE'

--it removes all of substrings when replace_string is omitted
SELECT REPLACE('12345abcdeabcde','abcde');

replace('12345abcdeabcde', 'abcde')
======================
  '12345'

The following shows how to print out the newline as “\n”.

-- no_backslash_escapes=yes (default)

CREATE TABLE tbl (cmt_no INT PRIMARY KEY, cmt VARCHAR(1024));
INSERT INTO tbl VALUES (1234,
'This is a test for

 new line.');

SELECT REPLACE(cmt, CHR(10), '\n')
FROM tbl
WHERE cmt_no=1234;

This is a test for\n\n new line.

REVERSE

REVERSE(string)

The REVERSE function returns string converted in the reverse order.

Parameters:

string – Specifies an input character string. If the value is an empty string, empty value is returned. If the value is NULL, NULL is returned.

Return type:

STRING

SELECT REVERSE('CUBRID');

 reverse('CUBRID')
======================
  'DIRBUC'

RIGHT

RIGHT(string, length)

The RIGHT function returns a length number of characters from the rightmost string. If any of the arguments is NULL, NULL is returned. If a value greater than the length of the string or a negative number is specified for a length, the entire string is returned. To extract a length number of characters from the leftmost string, use the LEFT().

Parameters:

• string – Input string

• length – The length of a string to be returned

Return type:

STRING

SELECT RIGHT('CUBRID', 3);

 right('CUBRID', 3)
======================
  'RID'

SELECT RIGHT ('CUBRID', 10);

 right('CUBRID', 10)
======================
  'CUBRID'

RPAD

RPAD(char1, n[, char2])

The RPAD function pads the right side of a string until the string length reaches the specified value.

Parameters:

• char1 – Specifies the string to pad characters to. If n is smaller than the length of char1, padding is not performed, and char1 is truncated to length n and then returned. If the value is NULL, NULL is specified.

• n – Specifies the total length of char1. If the value is NULL, NULL is specified.

• char2 – Specifies the string to pad to the right until the length of char1 reaches n. If it is not specified, empty characters (’ ‘) are used as a default. If the value is NULL, NULL is returned.

Return type:

STRING

Note

In versions lower than CUBRID 9.0, a single character is processed as 2 or 3 bytes in a multi-byte character set environment. If n is truncated up to the first byte representing a character according to a value of char1, the last byte is removed and a space character (1 byte) is added to the right because the last character cannot be represented normally. When the value is NULL, NULL is returned as its result.

--character set is UTF-8 for Korean characters

--it returns only 3 characters if not enough length is specified
SELECT RPAD ('CUBRID', 3, '?');

 rpad('CUBRID', 3, '?')
======================
  'CUB'

--on multi-byte charset, it returns the first character only with a right-padded space
SELECT RPAD ('큐브리드', 3, '?');

 rpad('큐브리드', 3, '?')
======================
  '큐브리'

--padding spaces on the right till char_length is 10
SELECT RPAD ('CUBRID', 10);

 rpad('CUBRID', 10)
======================
  'CUBRID    '

--padding specific characters on the right till char_length is 10
SELECT RPAD ('CUBRID', 10, '?');

 rpad('CUBRID', 10, '?')
======================
  'CUBRID????'

--padding specific characters on the right till char_length is 10
SELECT RPAD ('큐브리드', 10, '?');

 rpad('큐브리드', 10, '?')
======================
  '큐브리드??????'

--padding 4 characters on the right
SELECT RPAD ('큐브리드', LENGTH('큐브리드')+4, '?');

 rpad('',  char_length('')+4, '?')
======================
  '큐브리드????'

RTRIM

RTRIM(string[, trim_string])

The RTRIM function removes specified characters from the right-hand side of a string.

Parameters:

• string – Enters a string or string-type column to trim. If this value is NULL, NULL is returned.

• trim_string – You can specify a specific string to be removed in the right side of string. If it is not specified, empty characters (’ ‘) is automatically specified so that the empty characters in the right side are removed.

Return type:

STRING

SELECT RTRIM ('     Olympic     ');

 rtrim('     Olympic     ')
======================
  '     Olympic'

--If NULL is specified, it returns NULL
SELECT RTRIM ('iiiiiOlympiciiiii', NULL);

 rtrim('iiiiiOlympiciiiii', null)
======================
  NULL

-- trimming specific strings on the right
SELECT RTRIM ('iiiiiOlympiciiiii', 'i');

 rtrim('iiiiiOlympiciiiii', 'i')
======================
  'iiiiiOlympic'

SPACE

SPACE(N)

The SPACE function returns as many empty strings as the number specified. The return value is a VARCHAR type.

Parameters:

N – Space count. It cannot be greater than the value specified in the system parameter, string_max_size_bytes (default 1048576). If it exceeds the specified value, NULL will be returned. The maximum value is 33,554,432; if this length is exceeded, NULL will be returned. If you enter 0 or a negative number, an empty string will be returned; if you enter a type that can’t be converted to a numeric value, an error will be returned.

Return type:

STRING

SELECT SPACE(8);

   space(8)
======================
  '        '

SELECT LENGTH(space(1048576));

   char_length( space(1048576))
===============================
                        1048576

SELECT LENGTH(space(1048577));

   char_length( space(1048577))
===============================
                           NULL

-- string_max_size_bytes=33554432
SELECT LENGTH(space('33554432'));

   char_length( space('33554432'))
==================================
                          33554432

SELECT SPACE('aaa');

ERROR: Cannot coerce 'aaa' to type bigint.

STRCMP

STRCMP(string1, string2)

The STRCMP function compares two strings, string1 and string2, and returns 0 if they are identical, 1 if string1 is greater, or -1 if string1 is smaller. If any of the parameters is NULL, NULL is returned.

Parameters:

• string1 – A string to be compared

• string2 – A string to be compared

Return type:

INT

SELECT STRCMP('abc', 'abc');

0

SELECT STRCMP ('acc', 'abc');

1

Note

Until the previous version of 9.0, STRCMP did not distinguish an uppercase and a lowercase. From 9.0, it compares the strings case-sensitively. To make STRCMP case-insensitive, you should use case-insensitive collation(e.g.: utf8_en_ci).

-- In previous version of 9.0 STRCMP works case-insensitively
SELECT STRCMP ('ABC','abc');

0

-- From 9.0 version, STRCMP distinguish the uppercase and the lowercase when the collation is case-sensitive.
-- charset is en_US.iso88591

SELECT STRCMP ('ABC','abc');

-1

-- If the collation is case-insensitive, it does not distinguish the uppercase and the lowercase.
-- charset is en_US.iso88591

SELECT STRCMP ('ABC' COLLATE utf8_en_ci ,'abc' COLLATE utf8_en_ci);

0

SUBSTR

SUBSTR(string, position[, substring_length])

The SUBSTR function extracts a character string with the length of substring_length from a position, position, within character string, string, and then returns it.

Note

In the previous versions of CUBRID 9.0, the starting position and string length are calculated in byte unit, not in character unit; therefore, in a multi-byte character set, you must specify the parameter in consideration of the number of bytes representing a single character.

Parameters:

• string – Specifies the input character string. If the input value is NULL, NULL is returned.

• position – Specifies the position from where the string is to be extracted in bytes. Even though the position of the first character is specified as 1 or a negative number, it is considered as 1. If a value greater than the string length or NULL is specified, NULL is returned.

• substring_length – Specifies the length of the string to be extracted in bytes. If this argument is omitted, character strings between the given position, position, and the end of them are extracted. NULL cannot be specified as an argument value of this function. If 0 is specified, an empty string is returned; if a negative value is specified, NULL is returned.

Return type:

STRING

--character set is UTF-8 for Korean characters

--it returns empty string when substring_length is 0
SELECT SUBSTR('12345abcdeabcde',6, 0);

 substr('12345abcdeabcde', 6, 0)
======================
  ''

--it returns 4-length substrings counting from the position
SELECT SUBSTR('12345abcdeabcde', 6, 4), SUBSTR('12345abcdeabcde', -6, 4);

 substr('12345abcdeabcde', 6, 4)   substr('12345abcdeabcde', -6, 4)
============================================
  'abcd'                'eabc'

--it returns substrings counting from the position to the end
SELECT SUBSTR('12345abcdeabcde', 6), SUBSTR('12345abcdeabcde', -6);

 substr('12345abcdeabcde', 6)   substr('12345abcdeabcde', -6)
============================================
  'abcdeabcde'          'eabcde'

-- it returns 4-length substrings counting from 11th position
SELECT SUBSTR ('12345가나다라마가나다라마', 11 , 4);

 substr('12345가나다라마가나다라마', 11 , 4)
======================
  '가나다라'

SUBSTRING

SUBSTRING ( string, position [, substring_length]),

SUBSTRING(string FROM position [FOR substring_length] )

The SUBSTRING function, operating like SUBSTR, extracts a character string having the length of substring_length from a position, position, within character string, string, and returns it. If a negative number is specified as the position value, the SUBSTRING function calculates the position from the beginning of the string. And SUBSTR function calculates the position from the end of the string. If a negative number is specified as the substring_length value, the SUBSTRING function handles the argument is omitted, but the SUBSTR function returns NULL.

Parameters:

• string – Specifies the input character string. If the input value is NULL, NULL is returned.

• position – Specifies the position from where the string is to be extracted. If the position of the first character is specified as 0 or a negative number, it is considered as 1. If a value greater than the string length is specified, an empty string is returned. If NULL, NULL is returned.

• substring_length – Specifies the length of the string to be extracted. If this argument is omitted, character strings between the given position, position, and the end of them are extracted. NULL cannot be specified as an argument value of this function. If 0 is specified, an empty string is returned; if a negative value is specified, NULL is returned.

Return type:

STRING

SELECT SUBSTRING('12345abcdeabcde', -6 ,4), SUBSTR('12345abcdeabcde', -6 ,4);

  substring('12345abcdeabcde' from -6 for 4)   substr('12345abcdeabcde', -6, 4)
============================================
  '1234'                'eabc'

SELECT SUBSTRING('12345abcdeabcde', 16), SUBSTR('12345abcdeabcde', 16);

  substring('12345abcdeabcde' from 16)   substr('12345abcdeabcde', 16)
============================================
  ''                    NULL

SELECT SUBSTRING('12345abcdeabcde', 6, -4), SUBSTR('12345abcdeabcde', 6, -4);

  substring('12345abcdeabcde' from 6 for -4)   substr('12345abcdeabcde', 6, -4)
============================================
  'abcdeabcde'          NULL

SUBSTRING_INDEX

SUBSTRING_INDEX(string, delim, count)

The SUBSTRING_INDEX function counts the separators included in the partial character string and will return the partial character string before the count-th separator. The return value is a VARCHAR type.

Parameters:

• string – Input character string. The maximum length is 33,554,432 and if this length is exceeded, NULL will be returned.

• delim – Delimiter. It is case-sensitive.

• count – Delimiter occurrence count. If you enter a positive number, it counts the character string from the left and if you enter a negative number, it counts it from the right. If it is 0, an empty string will be returned. If the type cannot be converted, an error will be returned.

Return type:

STRING

SELECT SUBSTRING_INDEX('www.cubrid.org','.','2');

  substring_index('www.cubrid.org', '.', '2')
======================
  'www.cubrid'

SELECT SUBSTRING_INDEX('www.cubrid.org','.','2.3');

  substring_index('www.cubrid.org', '.', '2.3')
======================
  'www.cubrid'

SELECT SUBSTRING_INDEX('www.cubrid.org',':','2.3');

  substring_index('www.cubrid.org', ':', '2.3')
======================
  'www.cubrid.org'

SELECT SUBSTRING_INDEX('www.cubrid.org','cubrid',1);

  substring_index('www.cubrid.org', 'cubrid', 1)
======================
  'www.'

SELECT SUBSTRING_INDEX('www.cubrid.org','.',100);

  substring_index('www.cubrid.org', '.', 100)
======================
  'www.cubrid.org'

TO_BASE64

TO_BASE64(str)

Returns the result as the transformed base-64 string. If the input argument is not a string, it is changed into a string before it is transformed. If the input argument is NULL, it returns NULL. The base-64 encoded string can be decoded with FROM_BASE64() function.

Parameters:

str – Input string

Return type:

STRING

SELECT TO_BASE64('abcd'), FROM_BASE64(TO_BASE64('abcd'));

   to_base64('abcd') from_base64( to_base64('abcd'))
============================================
  'YWJjZA==' 'abcd'

The following is rules for TO_BASE64() function and FROM_BASE64().

• The encoded character for the alphabet value 62 is ‘+’.

• The encoded character for the alphabet value 63 is ‘/’.

• The encoded result consists of character groups, and each group has 4 characters which can be printed out. The 3 bytes of the input data are encoded into 4 bytes. If the last group are not filled with 4 characters, ‘=’ character is padded into that group and 4 characters are made.

• To divide the long output into the several lines, a newline is added into each 76 encoded output characters.

• Decoding process indicates newline, carriage return, tab, and space and ignore them.

See also

FROM_BASE64()

TRANSLATE

TRANSLATE(string, from_substring, to_substring)

The TRANSLATE function replaces a character into the character specified in to_substring if the character exists in the specified string. Correspondence relationship is determined based on the order of characters specified in from_substring and to_substring. Any characters in from_substring that do not have one on one relationship to to_substring are all removed. This function is working like the REPLACE() but the argument of to_substring cannot be omitted in this function.

Parameters:

• string – Specifies the original string. If the value is NULL, NULL is returned.

• from_substring – Specifies the string to be retrieved. If the value is NULL, NULL is returned.

• to_substring – Specifies the character string in the from_substring to be replaced. It cannot be omitted. If the value is NULL, NULL is returned.

Return type:

STRING

--it returns NULL when an argument is specified with NULL value
SELECT TRANSLATE('12345abcdeabcde','abcde', NULL);

  translate('12345abcdeabcde', 'abcde', null)
======================
  NULL

--it translates 'a','b','c','d','e' into '1', '2', '3', '4', '5' respectively
SELECT TRANSLATE('12345abcdeabcde', 'abcde', '12345');

  translate('12345abcdeabcde', 'abcde', '12345')
======================
  '123451234512345'

--it translates 'a','b','c' into '1', '2', '3' respectively and removes 'd's and 'e's
SELECT TRANSLATE('12345abcdeabcde','abcde', '123');

  translate('12345abcdeabcde', 'abcde', '123')
======================
  '12345123123'

--it removes 'a's,'b's,'c's,'d's, and 'e's in the string
SELECT TRANSLATE('12345abcdeabcde','abcde', '');

  translate('12345abcdeabcde', 'abcde', '')
======================
  '12345'

--it only translates 'a','b','c' into '3', '4', '5' respectively
SELECT TRANSLATE('12345abcdeabcde','ABabc', '12345');

  translate('12345abcdeabcde', 'ABabc', '12345')
======================
  '12345345de345de'

TRIM

TRIM([ [ LEADING | TRAILING | BOTH ] [ trim_string ] FROM ] string )

The TRIM function removes specific characters which are located before and after the string.

Parameters:

• trim_string – Specifies a specific string to be removed that is in front of or at the back of the target string. If it is not specified, an empty character (’ ‘) is automatically specified so that spaces in front of or at the back of the target string are removed.

• string – Enters a string or string-type column to trim. If this value is NULL, NULL is returned.

Return type:

STRING

• [LEADING|TRAILING|BOTH] : You can specify an option to trim a specified string that is in a certain position of the target string. If it is LEADING, trimming is performed in front of a character string if it is TRAILING, trimming is performed at the back of a character string if it is BOTH, trimming is performed in front and at the back of a character string. If the option is not specified, BOTH is specified by default.

• The character string of trim_string and string should have the same character set.

--trimming NULL returns NULL
SELECT TRIM (NULL);

 trim(both  from null)
======================
  NULL

--trimming spaces on both leading and trailing parts
SELECT TRIM ('     Olympic     ');

 trim(both  from '     Olympic     ')
======================
  'Olympic'

--trimming specific strings on both leading and trailing parts
SELECT TRIM ('i' FROM 'iiiiiOlympiciiiii');

 trim(both 'i' from 'iiiiiOlympiciiiii')
======================
  'Olympic'

--trimming specific strings on the leading part
SELECT TRIM (LEADING 'i' FROM 'iiiiiOlympiciiiii');

 trim(leading 'i' from 'iiiiiOlympiciiiii')
======================
  'Olympiciiiii'

--trimming specific strings on the trailing part
SELECT TRIM (TRAILING 'i' FROM 'iiiiiOlympiciiiii');

 trim(trailing 'i' from 'iiiiiOlympiciiiii')
======================
  'iiiiiOlympic'

UCASE, UPPER

UCASE(string)

UPPER(string)

The function UCASE or UPPER converts lowercase characters that are included in a character string to uppercase characters.

Parameters:

string – Specifies the string in which lowercase characters are to be converted to uppercase. If the value is NULL, NULL is returned.

Return type:

STRING

SELECT UPPER('');

 upper('')
======================
  ''

SELECT UPPER(NULL);

 upper(null)
======================
  NULL

SELECT UPPER('Cubrid');

 upper('Cubrid')
======================
  'CUBRID'

Note that the UPPER function may not work properly by specified collation. For example, when you try to change character ‘ă’ used in Romanian as upper character, this function works as follows by collation.

If collation is utf8_bin, it is not changed.

SET NAMES utf8 COLLATE utf8_bin;
SELECT UPPER('ă');

   upper(_utf8'ă')
======================
  'ă'

If collation is utf8_ro_RO, this can be changed.

SET NAMES utf8 COLLATE utf8_ro_cs;
SELECT UPPER('ă');

   upper(_utf8'ă' COLLATE utf8_ro_cs)
======================
  'Ă'

Regarding collations which CUBRID supports, see CUBRID Collation.