Common string functions include the string length calculation functions LENGTH() and CHAR_LENGTH(), string truncation function SUBSTR(), string concatenation functions CONCAT() and CONCAT_WS(), string uppercase conversion functions UPPER() and LOWER(), and string prefix and suffix deletion function LTRIM(), RTRIM(), and TRIM().
This topic describes how to use string functions in queries and provides examples.
Example: Use the string functions CONCAT(), CONCAT_WS(), UPPER(), and LOWER() to query data
/*Create a table and insert proper data.*/
obclient> CREATE TABLE cust (
c_id INTEGER,
c_first VARCHAR(20),
c_last VARCHAR(20),
c_credit DECIMAL(10, 2)
);
Query OK, 0 rows affected
obclient> INSERT INTO cust VALUES(101,'Ann','Smith',16.10);
1 row in set
obclient> INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row in set
obclient> INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row in set
obclient> SELECT CHAR_LENGTH(c_last) namelength
, CONCAT(c_first, c_last) full_name1
, CONCAT_WS('_', c_first, c_last) full_name2
, SUBSTR(c_last, 3)
, UPPER(c_last)
FROM cust ORDER BY c_last LIMIT 3;
+------------+------------------+-------------------+-------------------+---------------+
| namelength | full_name1 | full_name2 | SUBSTR(c_last, 3) | UPPER(c_last) |
+------------+------------------+-------------------+-------------------+---------------+
| 5 | MichaelBrown | Michael_Brown | own | BROWN |
| 7 | MadeleineJohnson | Madeleine_Johnson | hnson | JOHNSON |
| 5 | AnnSmith | Ann_Smith | ith | SMITH |
+------------+------------------+-------------------+-------------------+---------------+
3 rows in set
In the MySQL mode of OceanBase Database, the LENGTH() function uses byte as the length unit of strings, and the CHAR_LENGTH() function uses character as the length unit of strings. Sample code:
obclient> SELECT CHAR_LENGTH('AAA'), LENGTH('aaa');
+--------------------+---------------+
| CHAR_LENGTH('AAA') | LENGTH('aaa') |
+--------------------+---------------+
| 3 | 3 |
+--------------------+---------------+
1 row in set