SQL: Using String Functions in Queries
So in my past few posts, I have written about databases, creating tables, entering and updating data in those tables. In my introduction to SQL, I wrote a few examples of queries in the language. In this post, I will dig a little deeper into queries, specifically using string functions.
There are many string functions that one can use in SQL, however there are a few that are used more often than others. We will go over a few of the most common string functions one might need to use with examples.
The CONCAT Function
There are times when you will want to combine different column information into one result from the query. Take, for example, a table with personal information. When you sign up for certain services, the provider will ask you to enter your first and last name separately. When you submit the form, that information will enter a table with a column for first names and a column for last names. But what if you want the full name of your clients in a document? This is where the CONCAT function can be of use.
The CONCAT function will return a concatenated string of data entered. So if we wrote the following:
SELECT CONCAT(first_name, last_name) FROM person_table;an example of the return would be JohnSmith . The reason for this is that there was no separator between the information. So it just combined the two names together into one word.
Thankfully, the CONCAT function can combine more than two pieces of data together at once and it does not necessarily need to be a column, it could be a string. Using the previous example, we want each returned observation to have a space between the first and last name. One way to do this is:
SELECT CONCAT(first_name, ' ', last_name) FROM person_table;An example of the return would then be John Smith . However, this could increase the length and complexity of the query, if there are more than two or three columns that each need a space in between. An extension to the CONCAT function is CONCAT_WS, which returns a concatenated string with a separator in between the data. The basic formula for the CONCAT_WS function is:
CONCAT_WS(<separator>, <data>)The first variable is what you want to use to separate the pieces of data. The following variables are the different pieces of data you want to combine together. So the previous example could be rewritten as:
SELECT CONCAT_WS(' ', first_name, last_name) FROM person_table;This function is most useful in decreasing the possible complexity of a query.
The SUBSTRING Function
The SUBSTRING Function (SUBSTR can also be used) does what is sounds like. It allows the programmer to grab a certain section of a string. An example to consider is when you see an article on your phone and it will have a short blurb on what the article is about, then have a ... followed by a read more link.
The SUBSTRING function formula is
SUBSTRING(<string>, <starting_index>, <ending_index>)Each character in a string is represented by an index number. In MySQL, the index number starts at one. In other languages it is common to see the index number starting at zero. So, if the required substring was the first 25 characters of the article, we could use:
SELECT SUBSTRING(text, 1, 25) FROM article_table;Also it is important to note that different string functions can be combined into the same query. So we could add the ... to the end of the substring. To do so, we could combine the CONCAT and SUBSTRING functions for the final product.
SELECT CONCAT(SUBSTRING(text, 1, 25), '...') FROM article_table;The CHAR_LENGTH Function
The CHAR_LENGTH function (CHARACTER_LENGTH can also be used) counts the number of characters in the string. Take for example needing the character length of the tweets. There could be a table of tweets from a certain user and we need to know that length of tweets the person types.
This function is easy to use. The basic formula of the function is CHAR_LENGTH(<text>) . So in the case of a table of tweets we could code the following:
SELECT CHAR_LENGTH(tweet_text) FROM tweets_table;The REPLACE Function
As it sounds like, the REPLACE function will replace all cases of a specific string with another string. An example of this is when we consider the different symbols we might find in tweets. We could replace them with an empty string. The formula for the replace function is REPLACE(<string>, <string_replaced>, <new_string>) . So, if for some reason we wanted to replace the # symbol with an empty space, we could do so with the following:
SELECT REPLACE(tweet_text, '#', ' ') FROM tweets_table;The UPPER and LOWER Function
The last functions we will go over in this post are the UPPER and LOWER function. As you might expect, if you are experienced in other programming languages, the UPPER and LOWER function will return the string all in upper case or all in lowercase respectively. The formula for these functions are simple being UPPER(<string>)or LOWER(<string>). To give an example, if we wanted to the first name and last name in our first example to be all in lowercase, we could do so by the following.
SELECT LOWER(first_name), LOWER(last_name) FROM person_table;A Note on Return Column Names
Something to note: if you use a string function on a column from a table, the new column name will contain the method used. To rename the new column, the AS statement can be used. If we go back to our full name is example, instead of having a long and confusing column name, we could instead write the query as:
SELECT CONCAT(first_name, ' ', last_name) AS 'full name'
FROM person_table;I hope this post helps your understanding and foundation in using string functions for queries in SQL. Thank you for reading.
The documentation on the rest of the string functions and operators can be found in the link below.
