Using SQL SUBSTR Function to Extract and Manipulate String Data

The 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_position is 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 employees.

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 Emma Johnson.

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.

Happy learning!