Splitting and concatenating text

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Substring

SELECT left('abcde', 2),   -- first 2 characters
       right('abcde', 2);  -- last 2 characters

 left | right 
------+-------
 ab   | de

SELECT left('abc', 10), 
       length(left('abc', 10));

 left | length 
------+--------
 abc  |      3

SELECT substring(string FROM start FOR length);

SELECT substring('abcdef' FROM 2 FOR 3);

bcd

SELECT substr('abcdef', 2, 3);

some text,more text,still more text
         ^         ^
     delimiter  delimiter

Fields/chunks:

SELECT split_part(string, delimiter, part);

SELECT split_part('a,bc,d', ',', 2);

bc

SELECT split_part('cats and dogs and fish', ' and ', 1);

cats

SELECT concat('a', 2, 'cc');

a2cc

SELECT 'a' || 2 || 'cc';

a2cc

SELECT concat('a', NULL, 'cc');

acc

SELECT 'a' || NULL || 'cc';

Exploratory Data Analysis in SQL