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.
obclient> SELECT CHAR_LENGTH('OceanBase'), LENGTH('OceanBase'),CHAR_LENGTH('hello');
+--------------------------+---------------------+----------------------+
| CHAR_LENGTH('OceanBase') | LENGTH('OceanBase') | CHAR_LENGTH('hello') |
+--------------------------+---------------------+----------------------+
| 9 | 9 | 5 |
+--------------------------+---------------------+----------------------+
1 row in set