SQL String, Date, and Miscellaneous Functions
String Functions
Please do not copy without permission. © ALX 2024.
SQL string, date, and miscellaneous functions
Introduction
| String functions in SQL are built-in functions that operate on string data types (for
example, VARCHAR, CHAR, TEXT) and allow us to manipulate and work with strings.
Benefits of SQL string functions
● Data manipulation: Transform string data to meet
● We often work with data that are not in a
specific requirements.
format or structure that is immediately usable ● Data cleansing: Standardize data for improved
for the use case at hand. quality and consistency.
● Query flexibility: Construct complex queries by
● SQL string manipulation assists in turning
combining string functions with other SQL elements.
unstructured data into a structured format so
● Reporting and analysis: Extract meaningful
that generic transformations can be information and present structured data.
performed on the data. ● Database maintenance: Efficiently update, modify,
and correct data values within tables.
● Compatibility: Ensure compatibility across different
database management systems.
2
SQL string, date, and miscellaneous functions
Data overview
|
To explain SQL string functions, we will use a table, called Water_sources_sa_2022, that represents water
sources in South Africa for the year 2022, their types (surface water or groundwater), and their
availability levels (high, medium, or low).
Source_id Source_name Water_type Availability
1 Orange River Surface Water High
2 Karoo Aquifer Groundwater Medium
3 Vaal Dam Surface Water Medium
4 Table Mountain Spring Groundwater Low
5 Kruger National Park River Surface Water High
6 Cape Town Reservoir Surface Water Low
3
SQL string, date, and miscellaneous functions
UPPER() and LOWER() functions
| The UPPER() function is used to convert a string to uppercase while the LOWER() function is
used to convert a string to lowercase. Their syntaxes are as follows:
SELECT
UPPER(string) AS Alias
FROM
Table_name;
The strings to be converted.
SELECT
LOWER(string) AS Alias
FROM
Table_name;
4
SQL string, date, and miscellaneous functions
UPPER() and LOWER() functions
| If we wish to change the case of the values in the Source_name column, we can utilise the
UPPER() and LOWER() functions.
Query Output
Upper_source_name Lower_source_name
ORANGE RIVER orange river
SELECT
KAROO AQUIFER karoo aquifer
UPPER(Source_name) AS Upper_source_name,
LOWER(Source_name) AS Lower_source_name VAAL DAM vaal dam
FROM TABLE MOUNTAIN SPRING table mountain spring
Water_sources_sa_2022;
KRUGER NATIONAL PARK RIVER kruger national park river
CAPE TOWN RESERVOIR cape town reservoir
5
SQL string, date, and miscellaneous functions
LTRIM() and RTRIM() functions
| The LTRIM() function is used to remove leading spaces from the left end of a string while
the RTRIM() function is used to remove trailing spaces from the right end of a string.
SELECT
LTRIM(string) AS Alias
FROM
Table_name;
The strings with leading or
trailing spaces.
SELECT
RTRIM(string) AS Alias
FROM
Table_name;
6
SQL string, date, and miscellaneous functions
LTRIM() function
| If we intend to eliminate the leading and trailing spaces from the column Water_type, we
can utilise the LTRIM() and RTRIM() functions respectively.
Query Output
Trimmed_water_type
SELECT
LTRIM(RTRIM(Water_type)) AS Trimmed_water_type Surface Water
FROM
Water_sources_sa_2022; Groundwater
Surface Water
Groundwater
Compare the Trimmed_water_type column to the original column, Surface Water
Water_type. Do you notice the spaces that have been removed?
Surface Water
7
SQL string, date, and miscellaneous functions
LENGTH() function
| The LENGTH() function is used to determine the length (number of characters) of a string.
It counts white spaces as part of the string length.
The string whose length is
SELECT to be determined.
LENGTH(string) AS Alias
FROM
Table_name;
8
SQL string, date, and miscellaneous functions
LENGTH() function
| If we want to determine the length of the names for all water sources, we can employ the
LENGTH() function.
Query Output
Source_name Name_length
Orange River 12
Karoo Aquifer 13
SELECT
Source_name, Vaal Dam 8
LENGTH(Source_name) AS Name_length
FROM Table Mountain Spring 21
Water_sources_sa_2022;
Kruger National Park River 26
Cape Town Reservoir 19
9
SQL string, date, and miscellaneous functions
POSITION() function
|
The POSITION() function is used to return the position (index) of the first occurrence of a
substring within a string. It takes two arguments: the substring to search for and the
string in which to search for that substring. It returns 0 if the substring is not found.
The substring to search for.
SELECT The SQL keyword used to
POSITION(substring IN string) AS Alias indicate that we are
FROM searching for a substring
Table_name; within a string.
The string within which the
substring is to be found.
10
SQL string, date, and miscellaneous functions
POSITION() function
| If we aim to locate the position, if any, of the word “River” in all entries of the Source_name
column, we can utilise the POSITION() function.
Query Output
Source_name Position
Orange River 8
SELECT
Source_name, Karoo Aquifer 0
POSITION('River' IN Source_name) AS
Vaal Dam 0
Position
FROM Table Mountain Spring 0
Water_sources_sa_2022;
Kruger National Park River 22
Cape Town Reservoir 0
11
SQL string, date, and miscellaneous functions
LEFT() and RIGHT() functions
|
The LEFT() function is used to extract a specified number of characters from the
beginning (leftmost side) of a string while the RIGHT() function is used to extract a
specified number of characters from the end (rightmost side) of a string.
The string from which we
want to extract characters.
SELECT SELECT
LEFT(string, length) AS Alias RIGHT(string, length) AS Alias
FROM FROM
Table_name; Table_name;
Specifies the number of
characters to be extracted
from the left or right side.
12
SQL string, date, and miscellaneous functions
LEFT() and RIGHT() functions
|
If we intend to retrieve the initial five characters and the last four characters from each
entry in the Source_name column, including any white space characters, we can use the
LEFT() and RIGHT() functions respectively.
Query Output
Source_name Left_name Right_name
Orange River Orang iver
SELECT
Source_name, Karoo Aquifer Karoo ifer
LEFT(Source_name, 5) AS Left_name,
Vaal Dam Vaal Dam
RIGHT(Source_name, 4) AS Right_name
FROM Table Mountain Spring Table ring
Water_sources_sa_2022;
Kruger National Park Kruge iver
River
Cape Town Reservoir Cape voir
13
SQL string, date, and miscellaneous functions
SUBSTRING() function
|
The SUBSTRING() function is used to extract a substring from a string. It takes three
arguments: the original string, the starting position of the substring, and optionally, the
length of the substring.
The string from which we
want to extract characters.
SELECT
SUBSTRING(string, start_position, length)
AS Alias
Specifies the position within
FROM the string where the
Table_name; extraction should begin.
Specifies the number of
If the length parameter isn’t specified, the SUBSTRING() characters to be included
function will return the remaining characters from the in the extracted substring.
starting position to the end of the string.
14
SQL string, date, and miscellaneous functions
SUBSTRING() function
| To obtain a substring from the Source_name column that starts at the first position and
spans five characters (including white spaces), we can utilize the SUBSTRING() function.
Query Output
Source_name Extracted_string
Orange River Orang
SELECT
Source_name, Karoo Aquifer Karoo
SUBSTRING(Source_name, 1, 5) AS
Vaal Dam Vaal
Extracted_string
FROM Table Mountain Spring Table
Water_sources_sa_2022;
Kruger National Park River Kruge
Cape Town Reservoir Cape
15
SQL string, date, and miscellaneous functions
CONCAT() function
|
The CONCAT() function is used to concatenate or join multiple strings together. It takes
two or more string arguments (separated by commas) and returns a single
concatenated string.
SELECT The strings you want to
CONCAT(string1, string2, ...) AS Alias concatenate. You can
FROM provide multiple strings as
arguments.
Table_name;
16
SQL string, date, and miscellaneous functions
CONCAT() function
|
To provide a summary of the availability status for all water sources, we can combine the
entries from the Source_name column with their corresponding values from the
Availability column using the CONCAT() function.
Query Output
Availability_status
Orange River availability is High
SELECT Karoo Aquifer availability is Medium
CONCAT(Source_name, ' availability is ',
Availability) AS Availability_status Vaal Dam availability is Medium
FROM
Table Mountain Spring availability is Low
Water_sources_sa_2022;
Kruger National Park River availability is High
Cape Town Reservoir availability is Low
17
SQL string, date, and miscellaneous functions
REPLACE() function
|
The REPLACE() function is used to replace all occurrences of a specified substring within a
string with a new substring. It takes three arguments: the original string, the substring to
be replaced, and the new substring.
The string in which the
replacement will be
performed.
SELECT
REPLACE(string, search_string,replacement_string)
Specifies the substring to be
AS Alias replaced.
FROM
Table_name;
The new substring that will
replace the occurrences of
the search_string.
18
SQL string, date, and miscellaneous functions
REPLACE() function
| To replace the word “River” with the word “Lake” on all entries of the Source_name column,
we can use the REPLACE() function.
Query Output
Source_name Modified_name
Orange River Orange Lake
SELECT
Karoo Aquifer Karoo Aquifer
Source_name,
REPLACE(Source_name, 'River', 'Lake') Vaal Dam Vaal Dam
AS Modified_name
Table Mountain Spring Table Mountain Spring
FROM
Water_sources_sa_2022; Kruger National Park
Kruger National Park Lake
River
Cape Town Reservoir Cape Town Reservoir
19