SQL Functions   «Prev  Next»
Lesson 3 SQL Strings and substrings
Objective Run queries that contain string and substring functions to see how they work.

SQL Strings and Substring Functions

Here are the most commonly used functions that you will use on character, or string, data.

Concatenation of strings

When you add two strings together, you concatenate them. This is helpful if you want to combine the first and last name fields to make the results read more easily, for example. You use this capability by simply using the plus sign (+) between the values you want to place together:

SELECT au_Fname + '' + au_Lname 
FROM Authors

Depending on the engine, if you use quotes with no spaces between them, the engine will automatically insert a space for you.

Substrings

Substring capabilities let you extract a portion of a string, for example, only the leftmost three characters or the middle four characters. When you use SUBSTRING, you simply indicate the information item to use, the start point, and the length to extract.
For example, if you want to get the first three characters of the au_Lname column, the following statement would do the trick:

SELECT SUBSTRING(au_Lname, 1, 3) 
FROM Authors

This will result in a new (unnamed) column containing only those first three characters.

String Containing Query - Exercise

Go to the exercise to see how these functions work.
String Containing Query - Exercise
Along with inserting characters into a string, you may have a need to extract a substring from a string. For this purpose, all three servers include the substring() function (although Oracle Database's version is called substr()), which extracts a specified number of characters starting at a specified position. The following example extracts five characters from a string starting at the ninth position:

mysql> SELECT SUBSTRING('goodbye cruel world', 9, 5);
+----------------------------------------+
| SUBSTRING('goodbye cruel world', 9, 5) |
+----------------------------------------+
| cruel |
+----------------------------------------+
1 row in set (0.00 sec)