SQL’s string functions are among its most important tools. In this post, we’ll look at five ways you can perform various operations on strings.
There are many SQL functions that let you “edit” string data. Some trim off unneeded spaces or characters; others tell you how long a string is. These functions give you a lot of opportunities to transform and work with strings, which makes your code more effective. They also make code easier to understand.
Every SQL developer should know how to work with strings. In this post, I’ll help you get started by explaining five common SQL string functions.
CONCAT(first_char, second_char, ... n_char)
CONCAT function combines two or more strings into one string. All entry_char inputs need to be CHAR, VARCHAR, or NCHAR data types.
I’ve written two simple examples that show how useful this command is:
SELECT CONCAT ('Vertabelo Academy is good', ' and great', ' and fantastic!') FROM DUAL; -------------------- Vertabelo Academy is good and great and fantastic!
As you can see,
CONCAT has taken the three strings I’ve entered – 1)
Vertabelo Academy is good'; 2) 'and great'; 3) 'and fantastic!' – and combined them into a new string. This technique is very useful when we want to present database information in a readable way.
Let’s assume we have a table called
patient that stores patients’ ID numbers, names, admission dates, and illnesses. We want to display each patient’s data in the most understandable way. The best option is to create a sentence, as shown below:
SELECT CONCAT(name, ' was admitted to St. Ann's Hospital on ', date, ' with ', illness) FROM patient WHERE patient_id = 447; ------------------ John Doe was admitted to St. Ann’s Hospital on 21-11-2016 with flu.
In many database systems,
CONCAT can be replaced by the string concatenation symbol “||”. This operator is compatible with SQL standards, but it does not work in every database – e.g. in SQL Server, you must use “+”.
REPLACE(entry_char, string_searching, string_replace)
REPLACE allows you to selectively replace or remove data from inside a string. It returns an entry_char where the value of string_searching is replaced with string_replace. If the string_replace value is null, then every value matching string_searching is deleted from the entry string.
Let’s see two examples of
REPLACE at work. Suppose we want to update part of a record:
SELECT REPLACE ('Vertabelo Academy is good!', 'good', 'great!') FROM DUAL; --------------------- Vertabelo Academy is great!
As you can see,
REPLACE has changed the
good value in
Vertabelo Academy is good!” to “great. The record now reads
Vertabelo Academy is great!
Now let’s try a more practical demonstration. Let’s say you have a table called
registry that stores employee names. An employee named Jannet (the only Jannet in the table, for the purposes of this illustration) got married and changed her last name. The REPLACE function allows us to update her record very easily:
UPDATE registry SET name = REPLACE(name, 'Kowalski', 'Novak') WHERE name LIKE 'Jannet%'
Jannet Kowalski is now officially Jannet Novak, thanks to the
SUBSTR(char, position, length)
SUBSTR takes a portion (or substring) from a string and returns it. Char defines what we want to use as the source of the substring; in the following example, it’s
Vertabelo Academy. The position is where the substring starts; 11 characters from the beginning, in this case. Finally, length defines how long the substring should be. Putting it all together, we get:
SELECT SUBSTR('Vertabelo Academy',11,7) FROM DUAL; ------------------- Academy
This function is widely used in removing characters from a large string and for adding characters into a string. Let’s say you have a table called
products that contains a product list. The product ID is composed of letters that identify the product type and numbers that show the amount on hand.
id ---------- AA91 AA55 BG66 WP21
Now suppose you want to add an underscore between the letters and numbers in the product ID. With
SUBSTR, it is easy:
UPDATE products SET id = CONCAT(SUBSTR(id, 1, 2), '_', SUBSTR(id, 3); id ---------- AA_91 AA_55 BG_66 WP_21
Let’s look at these commands.
UPDATE will change the
CONCAT connects the portions of string created by two
SUBSTR functions. Note that the second SUBSTR doesn’t have a third parameter – it will include all characters located after the position specified in the second parameter.
Remember that not all database systems use the same name for SQL functions. In SQL Server, the
SUBSTR function is called
SUBSTRING, but the same syntax applies.
#4. ASCII and CHR
CHR are two totally opposite functions.
ASCII looks at a single character and returns its ASCII number code (e.g. “V” is 86). If a string of characters is entered, this function will return a value for the first character and ignore the rest. CHR, on the other hand, takes the ASCII code number and returns the appropriate character. Give it an 86, and it will return a “V”.
Let’s imagine that you need to find everyone whose last name starts with an A. You’ve decided to use the ASCII code number to do this. First of all, let’s find the ASCII equivalent of “A”.
SELECT ASCII('A') FROM DUAL; ------------------- 65
So 65 is our number. Now we can find all the needed records:
SELECT * FROM workers WHERE SUBSTR(second_name, 1, 1) = CHR(65);
first_name second_name age --------- ------------ ------ Garry Amundsen 41 Adam Anderson 55 Elizabeth Alanfry 33
TRIM( [ [ LEADING | TRAILING | BOTH ] character FROM ] edit_char )
TRIM’s main job is removing all specified characters from the beginning part (leading), ending part (trailing), or both parts (both) of a specific string (
This instruction has a lot of parameters. First, you must to choose the part of the string where you want to cut characters. If you don’t,
TRIM will remove the defined characters from both the beginning and end of edit_char. Next, you must tell it what characters to remove. If this parameter is omitted, the function will remove only space characters. Finally, you define the string.
Let’s see how it looks in practice:
Remove leading and trailing spaces:
SELECT TRIM(' Academy2017 ') FROM DUAL; ------------------ “Academy”
SELECT TRIM ('' FROM ' Vertabelo2017 ') FROM DUAL; ------------------ "Vertabelo2017"
Remove trailing ‘2017’ from the string:
SELECT TRIM (TRAILING '2017' FROM 'Vertabelo2017') FROM DUAL; ------------------ "Vertabelo"
Remove both leading and trailing ‘20’ from the string:
SELECT TRIM (BOTH '20' FROM '2017VertabeloAcademy20') FROM DUAL; ------------------ "17VertabeloAcademy"
TRIM does not have the same syntax across databases. In SQL Server,
TRIM takes only the edit_char parameter and deletes all spaces from the both ends of the string.
LTRIM deletes leading spaces and
RTRIM deletes trailing spaces.
There are many SQL functions that allow us to manipulate strings. What we’ve discussed in this post is only a small fraction of SQL can do. If you would like learn more about SQL strings, Vertabelo Academy’s Standard SQL Functions course is waiting for you. There are exercises that deal specifically with string manipulation – try them as soon as you can!