LEFT function
LEFT function has the following syntax:
LEFT (character_expression , integer_expression )and returns the part of a character string starting at a specified number of characters from the left. So,
SELECT DISTINCT LEFT(name, 1)
FROM Ships
ORDER BY 1| [[ column ]] |
|---|
| NULL [[ value ]] |
And this is the way to get the table of codes of all alphabetical characters:
SELECT CHAR(ASCII('a')+ num-1) letter, ASCII('a')+ num - 1 [code]
FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
) x
WHERE ASCII('a')+ num -1 BETWEEN ASCII('a') AND ASCII('z')| [[ column ]] |
|---|
| NULL [[ value ]] |
Here we use an algorithm of generating of numerical sequence, which is referred to Chapter 8.
It is known that code values of lowercase letters and capital letters differ. That’s why in order to get the whole set without rewriting above query; it will be enough to write up the identical code to aforesaid:
UNION
SELECT CHAR(ASCII('A')+ num-1) letter, ASCII('A')+ num - 1 [code]
FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
) x
WHERE ASCII('A')+ num -1 BETWEEN ASCII('A') AND ASCII('Z')Now let’s consider the task of searching a substring in a string expression. The two functions can be used for this: CHARINDEX and PATINDEX. They both return a starting position (a position of the first character of a substring) of a substring in a character string.