| 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.
Substring from String
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)
String Containing Query - Exercise