Use string functions in queries
This topic provides specific examples on how to use string functions in queries.
String functions
General string functions include the string length calculation functions LENGTH() and CHAR_LENGTH(), string truncation function SUBSTR(), string concatenation functions CONCAT() and CONCAT_WS(), string case conversion functions UPPER() and LOWER(), and string prefix and suffix deletion functions LTRIM(), RTRIM(), and TRIM().
Examples
Create a table and insert proper data into the table.
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
Use string functions in a query
Use string functions CHAR_LENGTH(), CONCAT(), CONCAT_WS(), SUBSTR(), and UPPER() to query data.
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.