String Functions

SQL comes with many built-in functions for reading, writing, updating, and generally manipulating data in any way you see fit. The examples below utilize the SELECT statement to work with strings, allowing us to practice using these functions by only reading the data initially. A major benefit of this approach is that it provides a visual of what would happen if you decide to manipulate the same data.

String Functions

Below, you will find some of the more common SQL string functions that we will focus on in this class. For a more exhaustive list of string functions, you can visit SQL String Functions .

RTRIM

RTRIM removes whitespaces from the right of the last character.

SELECT RTRIM('Too many trailing spaces.     ');

Too many extra spaces.

LTRIM

LTRIM removes whitespaces from the left of the first character.

SELECT LTRIM('         Leading spaces');

Leading spaces

LEFT

LEFT returns the length of characters starting at the provided character index and moving left.

SELECT LEFT('hello there!', 7);

hello t

RIGHT

RIGHT returns the length of characters starting at the provided character index and moving right.

SELECT RIGHT('hello there!', 3);

re!

LEN

LEN returns the length of a string based on characters, not including trailing spaces.

SELECT LEN('hello there!'');

12

DATALENGTH

DATALENGTH returns the length of a string based on bytes, including trailing spaces.

SELECT DATALENGTH('hello there!  ')

14

CHARINDEX

CHARINDEX can use to find a specific character within a string and returns it’s index location.

SELECT CHARINDEX('log', 'catalogue');

5

SUBSTRING

SUBSTRING returns part of a string. The first number is the starting index location and the second number is the ending index location.

SELECT SUBSTRING('Strings are fun!', 4, 9);

ings are

REVERSE

REVERSE returns the provided string backwards.

SELECT REVERSE('Data Analysis');

sisylanA ataD

UPPER

UPPER returns a string in all upper case letters.

SELECT UPPER('taco');

TACO

LOWER

LOWER returns a string in all lower case letters.

SELECT LOWER('tACO');

taco

REPLACE

REPLACE replaces part of a string using the provided patterns.

SELECT REPLACE('Beach Streat', 'ea', 'ee');

Beech Street

CONCAT

CONCAT is commonly used to combine strings together and work with null values.

SELECT CONCAT('Alyce','Cat', 'Frey');

SELECT CONCAT('Alyce', NULL, 'Frey');

-- Example 1 output:
AlyceCatFrey

-- Null example output:
AlyceFrey

CONCAT_WS

CONCAT_WS allows you to combine strings together with a specified separator value. The separator can be anything you want.

SELECT CONCAT_WS(' =::= ',  'Alyce', 'Frey');

SELECT CONCAT_WS('  -  ',  'Alyce', NULL, 'Frey');

-- Example 1 output:
Alyce=::=Frey
-- Null example output:
Alyce - Frey

STUFF

STUFF inserts a string into another string. The first number indicates where to insert the new characters.

SELECT STUFF('Pumpkin Pie', 3, 0, 'Add Chars');

SELECT STUFF('Pumpkin Pie', 3, 5, 'Delete Chars');

-- Example 1 Output:
PuAdd Charsmpkin Pie
-- Example 2 Output:
PuDelete Chars Pie

Check Your Understanding