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:
SUBSTRING(Name, 5, 4) AS SubstringName
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. 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'