Use a string connector in a query

This topic provides examples of how to use a string connector in a query.

String connectors

In seekdb, string connectors include functions such as CONCAT() and CONCAT_WS(). By default, '||' is a logical operator that represents "OR". In a specified sql_mode, '||' can also be used as a string connector.

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 affected
INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
1 row affected
INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
1 row affected

Use a string connector in a query

• Use "||" as a string connector to query.

  If you add the PIPES_AS_CONCAT option to the value of the sql_mode variable, '||' can also be used as a string connector.

  SET SESSION sql_mode='PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
  Query OK, 0 rows affected
  
  SELECT c_first || ' ' || c_last  full_name FROM cust ORDER BY c_last LIMIT 2;
  +-------------------+
  | full_name         |
  +-------------------+
  | Michael Brown     |
  | Madeleine Johnson |
  +-------------------+
  2 rows in set

• Use the CONCAT_WS function to concatenate strings.

  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