SQL - String Functions

CONCAT

CONCAT(string1, string2, ..., string_n)

SELECT CONCAT('Hello', ' ', 'World');

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

CONCAT_WS

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM employees;

The CONCAT_WS function will ignore any NULL values, only joining the non-NULL values with the provided separator. Hence, ‘John NULL’ would become just ‘John’.

LENGTH

LENGTH ( string )

SELECT first_name, LENGTH(first_name) as length_of_first_name
FROM employees;

Usage with DISTINCT

LENGTH can also be used in conjunction with DISTINCT to find the number of distinct lengths of a specific field.

SELECT DISTINCT LENGTH(first_name) as distinct_length_of_first_name
FROM employees;

Usage with WHERE Clause

It can work in the WHERE clause to return only those records where the length of a specific field meets a certain condition.

SELECT *
FROM employees
WHERE LENGTH(first_name) > 4;

SUBSTRING

SUBSTRING(string, start, length)

Where:

string is the source string from which you want to extract.
start is the position to start extraction from. The first position in the string is always 1.
length is the number of characters to extract.

Example

SELECT SUBSTRING('Hello World', 1, 5) as ExtractedString;

Note
SQL is case-insensitive, meaning SUBSTRING, substring, and Substring will all function the same way.

SUBSTRING with FROM and FOR

SUBSTRING(string FROM start FOR length)

SELECT SUBSTRING('Hello World' FROM 1 FOR 5) as ExtractedString;

REPLACE

REPLACE(input_string, string_to_replace, replacement_string)

SELECT EmpId, EmpName,
REPLACE(EmpName, 'Doe', 'Roe') as ModifiedName
FROM Employees;

UPPER

UPPER(string)

SELECT UPPER(name) as 'Upper Case Name' FROM students;

LOWER

LOWER(string)

SELECT LOWER('SQL is BAE!') AS LowerCaseString;