Use string functions in queries

This topic provides examples of how to use string functions in queries.

String functions

Common string functions include the LENGTH() and CHAR_LENGTH() functions for calculating string length, the SUBSTR() function for extracting substrings, the CONCAT() and CONCAT_WS() functions for concatenating strings, the UPPER() and LOWER() functions for converting string case, and the LTRIM(), RTRIM(), and TRIM() functions for trimming string prefixes and suffixes.

Examples

Create a sample table and insert appropriate data.

CREATE TABLE cust (
       c_id        INTEGER,
       c_first     VARCHAR(20),
       c_last      VARCHAR(20),
       c_credit    DECIMAL(10, 2)
 );
Query OK, 0 rows affected

INSERT INTO cust VALUES(101,'Ann','Smith',16.10);
1 row in set 
INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row in set 
INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row in set 

Use string functions in queries

Use the CHAR_LENGTH(), CONCAT(), CONCAT_WS(), SUBSTR(), and UPPER() functions to query data.

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 seekdb, the LENGTH() function calculates string length in bytes, and the CHAR_LENGTH() function calculates string length in characters. Here is an example:

SELECT CHAR_LENGTH('China'), LENGTH('China'),CHAR_LENGTH('hello');
+-----------------------+------------------+----------------------+
| CHAR_LENGTH('China')  | LENGTH('China')  | CHAR_LENGTH('hello') |
+-----------------------+------------------+----------------------+
|                     2 |                6 |                    5 |
+-----------------------+------------------+----------------------+
1 row in set