5 SQL Functions for Manipulating Strings

Let’s see two examples of REPLACE at work. Suppose we want to update part of a record:

SELECT REPLACE ('LearnSQL is good!', 'good', 'great!') 
FROM DUAL;
---------------------

LearnSQL is great!

As you can see, REPLACE has changed the good value in LearnSQL is good!” to “great. The record now reads LearnSQL is great!

Now let’s try a more practical demonstration of manipulating strings with this function. 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 REPLACE function.

SQL String Functions: SUBSTR

There are even more SQL functions used for manipulating strings. SUBSTR(char, position, length)

SUBSTR takes a portion (or substring) from a SQL string and returns it. Char defines what we want to use as the source of the substring; in the following example, it’s LearnSQL. The position is where the substring starts; 6 characters from the beginning, in this case. Finally, length defines how long the substring should be. Putting it all together, we get:

SELECT SUBSTR('LearnSQL',6,3) 
FROM DUAL;
-------------------

SQL

This SQL string 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 CONCAT and 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
products table. 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 server string functions. In SQL Server, the SUBSTR function is called SUBSTRING, but the same syntax applies. Click here to learn more about SQL Server.

SQL String Functions: ASCII and CHR

ASCII(single_character)
CHR(character_code)

ASCII and CHR are two totally opposite SQL 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 SQL string 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

SQL String Functions: TRIM

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 (edit_char).

This instruction has a lot of parameters. First, you must 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('    SQL2017    ')
FROM DUAL;
------------------

“SQL2017”

SELECT TRIM ('' FROM  '   SQL2017   ')
FROM DUAL;
------------------

"SQL2017"

Remove trailing ‘2017’ from the string:

SELECT TRIM (TRAILING '2017' FROM 'SQL2017')
FROM DUAL;
------------------

"SQL"

Remove both leading and trailing ‘20’ from the string:

SELECT TRIM (BOTH '20' FROM '2017LearnSQL20')
FROM DUAL;
------------------

"17LearnSQL"

Unfortunately, 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.

Conclusion

There are many SQL functions that allow us to manipulate strings. What we’ve discussed in this post is only a small fraction of what SQL functions can do. To learn SQL string functions in detail, check out our LearnSQL.com’s Standard SQL Functions online course.