String functions

This topic provides examples for each string function.

SUBSTR/SUBSTRING

Both the SUBSTR and SUBSTRING functions extract a substring from a string. The following takes the SUBSTR function as an example. Currently, the following syntaxes are supported:

• substr(str, pos)

  • str: the original string from which the substring is to be extracted.

  • pos: the index of the start position for extracting the substring.

• substr(str, pos, len)

  • str: the original string from which the substring is to be extracted.

  • pos: the index of the start position for extracting the substring.

  • len: the number of characters in the substring to be extracted.

The substr(str from pos) or substr(str from pos for len) syntax is not supported.

Here are some examples of the SUBSTR function:

• Select rows where c1 is lo from table t1. OceanBase Database Proxy (ODP) can accurately route the SQL query.

  select * from t1 where c1=substr('hello',4);
  

• Select rows where c1 is hel from table t1. ODP can accurately route the SQL query.

  select * from t1 where c1=substr('hello',1,3);
  

TO_CHAR

The TO_CHAR function converts data of the datetime type to that of the char type. Currently, ODP supports only the to_char (datetime, [fmt]) syntax where the first argument is of the date or timestamp type.

Example: Select rows where c1 equals the current system date in the format of YYYY/MM/DD from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=to_char(sysdate,'YYYY/MM/DD');

LOWER/LCASE

The LOWER and LCASE functions convert characters into lowercase. The following takes the LOWER function as an example. Currently, ODP supports the lower(str) syntax.

Example: Select rows where c1 is aaaa from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=lower('aAAA');

UPPER/UCASE

The UPPER and UCASE functions convert characters into uppercase. The following takes the UPPER function as an example. Currently, ODP supports the upper(str) syntax.

Example: Select rows where c1 is ASSDS from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=upper('ASsds');

TRIM

The TRIM function removes the specified leading and/or trailing characters from a string. By default, spaces are removed. Currently, ODP supports the following syntaxes:

• trim ([BOTH| LEADING | TRAILING] remove_str FROM str)

  • BOTH: removes the specified leading and trailing characters from a string. This parameter is optional.

  • LEADING: removes the specified leading characters from a string. This parameter is optional.

  • TRAILING: removes the specified trailing characters from a string. This parameter is optional.

  • remove_str: the characters to be removed from the original string str. In the Oracle mode of OceanBase Database, the size of this parameter is one character.

  • str: the original string from which the specified remove_str is to be removed.

• trim(remove_str from str)

  • remove_str: the characters to be removed from the original string str. In the Oracle mode of OceanBase Database, the size of this parameter is one character.

  • str: the original string from which the specified remove_str is to be removed.

• trim(str)

Example: Select rows where c1 is #Hello_#_# from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=trim('_' from '_#Hello_#_#');

LTRIM

The LTRIM function removes the specified leading characters from a string. By default, spaces are removed. ODP supports the following syntaxes:

• MySQL mode: ltrim(str)

• Oracle mode: ltrim(str,[set])

  • str: the string from which the specified characters are to be removed.

  • set: the characters to be removed. This parameter is optional.

Example: Select rows where c1 equals ltrim(' 123'), that is, the 123 string, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1= ltrim('   123'); 

RTRIM

The RTRIM function removes the specified trailing characters from a string. By default, spaces are removed. ODP supports the following syntaxes:

• MySQL mode: rtrim(str)

• Oracle mode: rtrim(str,[set])

  • str: the string from which the specified characters are to be removed.

  • set: the characters to be removed. This parameter is optional.

Example: Select rows where c1 equals rtrim('Helloa_***#__','_*#'), that is, the Helloa string, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1= rtrim('Helloa_***#__','_*#'); 

CONCAT

The CONCAT function concatenates one or more strings into a new single string. ODP supports the concat(string1, string2, ..., stringN) syntax.

Example: Select rows where c1 equals concat('a','b'), that is, the ab string, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1 = concat('a','b');

REPLACE

The REPLACE function replaces a substring in a string with another substring. ODP supports the replace(str,from_str,to_str) syntax.

• str: the original string in which a substring is to be replaced.

• from_str: the substring to be replaced. If this substring is contained in str, it will be replaced with to_str.

• to_str: the new substring that replaces from_str.

Example: Select rows where c1 is heLLo from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=replace('hello','l','L');

LENGTH

The LENGTH function returns the length of a string. ODP supports the length(str) syntax.

Example: Select rows where c1 equals length('hello'), that is, 5, from table t1.

select * from t1 where c1=length('hello');