Introduction
Concatenation
Simple text functions
How to modify Strings
18. The SUBSTRING() function
Summary

Instruction

You're making great progress!

The functions we've learned so far act on entire string values. We can also manipulate parts of strings, or even single letters. We'll start off with the SUBSTRING() function. It looks like this:

SUBSTRING(text, start, length)

Here's how you'd use it to select part of a string:

SELECT
  SUBSTRING(Name, 5, 4) AS SubstringName
FROM Item;
a12

The first parameter of SUBSTRING() is the given text (above, the Name field). The second parameter tells the function where the substring should start. The indexing is 1-based (the first character in the string is available at index 1). In the above example, we are asking SUBSTRING to start the text fragment at its fifth character. The third parameter, length, indicates how many characters will be returned. In this case, if the name is N'TripCare', the starting index is 5, and the length is 4, we'll get N'Care' as the result. The database will start at the fifth character, which is 'C', and return the next four characters.

If the length or start position is NULL, SUBSTRING() returns NULL. If you set the length parameter to zero, the function returns an empty string. If you start from an index greater than the length of the whole string, it will return an empty string. And if you start with the index at 0 (before the first position), the last character will be removed from the returned string. So:

SELECT SUBSTRING('Anne', 1, 3);  -- returns 'Ann'
SELECT SUBSTRING('Anne', 0, 3);  -- returns 'An'

Exercise

Show the full name of each item and the first four characters of its name, starting from the first character and cutting all the spaces at the beggining and at the end of these 4 characters. Name the last column SubstringName.

Stuck? Here's a hint!

Use TRIM(SUBSTRING(Name, 1, 4)).