The following example uses the substring function. It displays the last name and first initial of each author, for example, “Bennet A”.
select au_lname, substring(au_fname, 1, 1) from authors
substring returns a portion of a character or binary string.
substring always takes three arguments. The first can be a character or binary string, a column name, or a string-valued expression that includes a column name. The second argument specifies the position at which the substring should begin. The third specifies the length, in number of characters, of the string to be returned.
The syntax of substring looks like this:
substring(expression, start, length)
For example, here is how to specify the second, third, and fourth characters of the string constant “abcdef”:
select x = substring("abcdef", 2, 3)
x --------- bcd (1 row affected)
The following example shows how to extract the lower four digits from a binary field, where each position represents two binary digits:
select substring(xactid,5,2) from syslogs
------ 0x0000 0x0001 0x0001 0x0001 0x0001 0x0001 0x0001 0x0001 0x0001 0x0001 0x0001 (11 rows affected)
