Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Concatenation
Simple text functions
Modifying strings
13. 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 with the SUBSTRING() function. It looks like this:

SUBSTRING(text FROM start FOR length)

or the shorter form:

SUBSTRING(text, start, length)

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

SELECT SUBSTRING(name, 5, 4) AS substring_name
FROM item;

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 the SUBSTRING() function to start the text fragment at the fifth character. The third parameter, length, indicates how many characters will be returned.

In this case, if the name is 'TripCare', the starting index is 5, and the length is 4, we'll get 'Care' as the result. The database will start at the fifth character, which is 'C', and return that character and the next three characters.

Exercise

Show the full name of each item and its first four characters after trimming. Name the last column new_name.

Stuck? Here's a hint!

Use the following expression:

SUBSTRING(TRIM(name), 1, 4)