Photo by Boitumelo Phetla on Unsplash
Using SQL SUBSTR Function to Extract and Manipulate String Data
2 min read
SUBSTR function is a commonly used function in SQL for manipulating string data. This function is used to extract a substring from a larger string based on a specified starting position and length. In this article, we will discuss the
SUBSTR function in SQL, its syntax, and its uses.
However, before that, let us take a minute to understand its syntax.
SUBSTR(string, start_position, length)
The syntax contains 3 parameters, which are explained further as follows:
string: Denotes the input string from which the substring is extracted
start_position: This specifies the starting position of the substring within the input string. If the
start_positionis negative, it is counted from the end of the string. Think of it as an index.
length: The parameter specifies the length of the substring to be extracted. It is important to specify it, otherwise, the whole string will be extracted from its start position to its end.
To understand the above function and its application, let us take an example.
Let us take the example of the table above named
Then, you are faced with a problem, where you are required to query a list of names from employees which start with a vowel.
The following code will execute and return
SELECT DISTINCT Name FROM employees WHERE LOWER(SUBSTR(Name, 1, 1)) IN ('a', 'e', 'i', 'o', 'u');
This query uses the
SUBSTR function to extract the first and last characters of each name, and then checks if they are vowels using the
IN operator. The
LOWER function is used to convert the extracted characters to lowercase for comparison with the list of vowels. The
DISTINCT keyword is used to remove any duplicate names from the result.
That was fast right?
The function has additional applications that could find potential applications in data manipulation and data handling. For instance, you can use the function to manipulate column values in a given table, say, to query the first name of a given column. Another interesting application is data cleaning, where it removes unwanted characters from a string.