This topic provides examples of how to use string concatenation operators in queries.
String concatenation operators
String concatenation functions include CONCAT() and CONCAT_WS(). By default, || represents the logical OR operator. However, under specific SQL mode settings, || can also be used as a string concatenation operator.
Examples
Create a sample table and insert appropriate 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 affected
obclient> INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row affected
obclient> INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row affected
Examples of using string concatenation operators in queries
Use
||as a string concatenation operator in queries.If you add the
PIPES_AS_CONCAToption to thesql_modevalue,||is also treated as a string concatenation operator.obclient> SET SESSION sql_mode='PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES'; Query OK, 0 rows affected obclient> SELECT c_first || ' ' || c_last full_name FROM cust ORDER BY c_last LIMIT 2; +-------------------+ | full_name | +-------------------+ | Michael Brown | | Madeleine Johnson | +-------------------+ 2 rows in setUse the
CONCAT_WSfunction to concatenate strings.obclient> SELECT CONCAT_WS(' ', c_first, c_last) full_name FROM cust ORDER BY c_last LIMIT 2; +-------------------+ | full_name | +-------------------+ | Michael Brown | | Madeleine Johnson | +-------------------+ 2 rows in set