String Functions

This YouTube video was created by Steve Griffith.

String Functions are used to alter the returned data of those columns using a string datatype. String Functions do not alter the actual data that is stored in the database as all functions are executed after the data is retrieved but before it is displayed.

When working with String Functions, a new string can be adding strings with quotes as an argument of the function. If a string argument has no strings, it will be treated as a column name.

UPPER() Function

The UPPER() function is used to convert all the letter characters of a string to uppercase.

-- Covert the each movie title to all uppercase
SELECT UPPER(movie_title)
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

LOWER() Function

The LOWER() function is used to convert all the letter characters of a string to lowercase.

-- Covert the each movie director to all lowercase
SELECT LOWER(director)
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

LENGTH() Function

The LENGTH() function will return the number of characters in a string.

-- Return the number of characters in each movie title
SELECT LENGTH(movie_title), movie_title
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

CONCAT() Function

The CONCAT() function is used to combine two or more strings.

-- Combine movie_title, director and movie_id 
SELECT CONCAT(movie_title, ' : ', director, ' : ', movie_id) AS con
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

CONCAT_WS() Function

The CONCAT_WS() function, which stands for Concatenate With Separator.

-- Combine movie_title, director and movie_id
-- with the specified separator
SELECT CONCAT_WS(' : ', movie_title, director, movie_id) AS con
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

TRIM() Function

The TRIM() function is used to remove any white space from the beginning and end of a string.

NOTE

There is also LTRIM() and RTRIM() which only removes white space from the beginning and the end, respectively.

-- Removes the whitespace from the beginning and end of a string
SELECT TRIM('            abcde                 ') AS tr
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

LEFT() Function

The LEFT() function is used to return the specified number of characters starting at the left side of the string.

NOTE

There is also RIGHT() which is used to return the specified number characters starting at the right side of the string.

-- Returns the first 5 characters of each movie title
SELECT LEFT(movie_title, 5) AS five
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

RPAD() Function

The RPAD() function appends the specified characters to a string until the string is the specified length. If the string is longer than the specified length the returned value will be shortened.

NOTE

There is also LPAD() which prepends the specified characters to a string until the string is the specified length.

-- Appends Xs to movie titles until each title is 50 characters long
SELECT RPAD(movie_title, 50, 'XXXXXX') AS xx
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle

FORMAT() Function

The FORMAT() function formats the provided number by rounding decimal places and adding commas and periods. A locale can be specified so that the proper separators are used. The resulting value of the FORMAT() function will be a string.

-- Formats the year to include 3 decimal places
SELECT FORMAT(year, 3) AS frmt
FROM `movies`
LIMIT 10;

-- Formats the year to include 3 decimal places
-- using the French Canadian
SELECT FORMAT(year, 3, 'fr_CA') AS frmt
FROM `movies`
LIMIT 10;

Try it yourself on DB Fiddle