Concatenation basics

  • concatenation functions:

    • Oracle - concat
    • MySQL - concat
    • MSSQL - concat
    • PostgreeSQL - concat, concat_ws(avoiding null)

  • concatenation symbol is

    • Oracle - ||
    • MySQL - || - you need to run: set sql_mode=PIPES_AS_CONCAT;
    • MSSQL - +
    • PostgreSQL - ||

* the escape characters * for all is - single quote - '

Oracle, MySQL Concatenation Example

Oracle and MySQL have almost identical SQL queries:

--set sql_mode=PIPES_AS_CONCAT; --for MySQL only
SELECT 'Text' || column || 'Text2' || 
column2|| ' Text3' column3 AS concat_column FROM table;

select CONCAT(
  CONCAT(
    CONCAT(
      CONCAT(
        CONCAT(' Text1', column1), 
        'Text2'), 
      column2),
    'Text3'),
  column3)
  from table

MSSQL Concatenation Example

MSSQL example of concatenation using function isnull and + :

SELECT 
    column1, 
    column2, 
    column1+ ' ' + column2 as concat_column
FROM table;
SELECT 
    column1, 
    column2, 
    concat(column1, ' ', column2) as concat_column
FROM table;
SELECT 
    column1, 
    column2, 
    ISNULL(column1)+ ' ' + ISNULL(column2) as concat_column
FROM table;

PostgreSQL Concatenation Example

Using concat_ws will take into account null values:

SELECT column1 || ', ' || column2 AS concat_column FROM table;

SELECT concat_ws(', ', column1, column2) AS concat_column FROM table;

SELECT concat(column1, column2) AS concat_column FROM table;

Useful SQL concatenation examples

count multiples tables

How to produce multiple select counts from all tables or by filter that you can apply.
As you can see from the examples below(three of them are working and doing one and the same - return count for all tables) mixing concat and concatenation symbols could lead to unreadable code. So it's better to avoid it. MySQL example:


SELECT 
'select count(*), '|| '''' || table_name || ''''|| ' from '|| 'TEST'||	'.'||	 table_name||';' AS count_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%e%' AND table_schema LIKE '%TEST%';


SELECT 
CONCAT
	(CONCAT
		(CONCAT
			(CONCAT('select count(*), ', ''''
 ||table_name
 ||''''), ' from '), CONCAT(CONCAT('TEST','.'), table_name)),';')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%e%' AND table_schema LIKE '%TEST%';


SELECT 
'select count(*), '||
 '''' ||
 table_name ||
 ''''||
 ' from '||
 'TEST'||
	'.'||
	 table_name||
	 ';' AS count_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%e%' AND table_schema LIKE '%TEST%';
  • Result:
select count(*), 'table1' from TEST.table1;
select count(*), 'table2' from TEST.table2;
select count(*), 'test' from TEST.test;

truncate multiples tables

SELECT 
'truncate table '|| table_name ||';' AS truncate_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%e%' AND table_schema LIKE '%TEST%';

Result:

truncate table table1;
truncate table table2;
truncate table test;

If you are interested in counting null values:

SQL Count not null values in every table column