Справка по SQL - W Cat

name

Revenge

Royal Sovereign

Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:

Вот, например, как можно определить имена кораблей, которые начинаются и заканчиваются на одну и ту же букву:

SELECT name FROM Ships WHERE LEFT(name, 1) = RIGHT(name, 1)

То, что в результате мы получим пустой результирующий набор, означает, что таких кораблей в базе данных нет. Давайте возьмем комбинацию значений - класс и имя корабля.

Соединение двух строковых значений в одно называется конкатенацией, и в SQL Server для этой операции используется знак "+" (в стандарте "||"). Итак,

SELECT * FROM (

SELECT class +' '+ name AS cn FROM Ships

) x

WHERE LEFT(cn, 1)=RIGHT(cn, 1)

Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:

cn

Iowa Missouri

North Carolina Washington

А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав

SELECT * FROM (

SELECT class +' '+ name AS cn FROM Ships

UNION ALL

SELECT 'a' as nc

) x

WHERE LEFT(cn, 1)=RIGHT(cn, 1)

, которая возвращает число символов в строке. Ограничимся случаем, когда число символов больше единицы:

SELECT * FROM (

SELECT class +' '+ name AS cn FROM Ships

UNION ALL

SELECT 'a' as nc

) x

WHERE LEFT(cn, 1)=RIGHT(cn, 1) AND LEN(cn)1

Замечание. Реализация этой функции в MS SQL Server имеет одну особенность, а именно, при подсчете длины не учитываются концевые пробелы.

Действительно, выполним следующий код:

DECLARE @chr AS CHAR(12), @vchr AS VARCHAR(12)

SELECT @chr = 'abcde' + REPLICATE(' ', 5), @vchr = 'abcde'+REPLICATE(' ', 5)

SELECT LEN(@chr), LEN(@vchr)

SELECT DATALENGTH(@chr), DATALENGTH(@vchr)

5

5

12

10

Функция REPLICATE дополняет константу 'abcde' пятью пробелами справа, которые не учитываются функцией LEN, - в обоих случаях получаем 5.

Функция DATALENGTH возвращает число байтов в представлении переменной и демонстрирует нам различие между типами CHAR и VARCHAR. DATALENGTH даст нам 12 для типа CHAR и 10 - для VARCHAR.

Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернула фактическую длину переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR - это тип фиксированной длины. Если значение переменной оказывается меньше ее длины, а длину мы объявили как CHAR(12), то значение переменной будет "выровнено" до требуемой длины за счет добавления концевых пробелов.

На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т.д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете ("2d") или скорость CD ("24x"). Проблема заключается в том, что текст сортируется так (по возрастанию)

11a

1a

2a

Действительно,

SELECT '1a' AS place

UNION ALL SELECT '2a'

UNION ALL SELECT '11a'

ORDER BY 1

Если же требуется упорядочить места в порядке возрастания рядов, то порядок должен быть такой

1a

2a

11a

Чтобы добиться такого порядка, нужно выполнить сортировку по числовым значениям, присутствующим в тексте. Можно предложить такой алгоритм:

1. Извлечь число из строки.

2. Привести его к числовому формату.

3. Выполнить сортировку по приведенному значению.

Т.к. нам известно, что буква только одна, то для извлечения числа из строки можно воспользоваться следующей конструкцией, которая не зависит от числа цифр в номере места:

LEFT(place, LEN(place)-1)

Если только этим и ограничиться, то получим

place

1a

11a

2a

Приведение к числовому формату может быть следующим:

CAST (LEFT(place, LEN(place)-1) AS INT)

Осталось выполнить сортировку

SELECT * FROM (

SELECT '1a' AS place

UNION ALL SELECT '2a'

UNION ALL SELECT '11a'

) x

ORDER BY CAST(LEFT(place, LEN(place)-1) AS INT)

Что и требовалось доказать.

Ранее мы для извлечения числа из текстовой строки пользовались функцией LEFT, т.к. нам было известно априори, какое число символов нужно убрать справа (один). А если же нужно извлечь строку из подстроки не по известной позиции символа, а по самому символу? Например: извлечь все символы до первой буквы "х" (значение скорости CD).

В этом случае мы можем использовать также уже рассмотренную ранее функцию CHARINDEX, которая позволит определить неизвестную позицию символа:

SELECT model, LEFT(cd, CHARINDEX('x', cd) -1) FROM PC

Функция SUBSTRING

SUBSTRING (<выражение, <начальная позиция, <длина )

Эта функция позволяет извлечь из выражения его часть заданной длины, начиная от заданной начальной позиции. Выражение может быть символьной или бинарной строкой, а также иметь тип text или image. Например, если нам потребуется получить 3 символа в названии корабля, начиная со 2-го символа, то сделать без помощи функции SUBSTRING будет не так просто. А так мы пишем:

SELECT name, SUBSTRING(name, 2, 3) FROM Ships

В случае, когда нужно извлечь все символы, начиная с некоторого, мы также можем использовать эту функцию. Например,

SELECT name, SUBSTRING(name, 2, LEN(name)) FROM Ships

даст нам все символы в названиях кораблей от второй буквы в имени. Обратите внимание на то, что для указания числа извлекаемых символов я использовал функцию LEN(name), которая возвращает число символов в имени. Понятно, что поскольку мне нужны символы, начиная со второго, то их число будет меньше общего количества символов в имени. Однако это не вызывает ошибки, поскольку если указанное число символов превышает возможное число, то будут извлечены все символы до конца строки. Поэтому я и беру их с запасом, не утруждая себя вычислениями.

Функция REVERSE

Эта функция переворачивает строку, как бы читая ее справа налево. Т.е. результатом запроса

SELECT REVERSE('abcdef')

будет 'fedcba'. Если бы в языке отсутствовала функция RIGHT, то запрос

SELECT RIGHT('abcdef',3)

можно было бы равносильно заменить запросом

SELECT REVERSE(LEFT(REVERSE('abcdef'),3))

Я вижу пользу этой функции в следующем. Пусть нам требуется определить позицию не первого, а последнего вхождения некоторого символа (или последовательности символов) в строке. Вспомним пример, в котором мы определяли позицию первого символа "а" в названии корабля "California":

SELECT CHARINDEX('a', name) first_a

FROM Ships WHERE name='California'

Определим теперь позицию последнего вхождения в это название символа "а". Функция

CHARINDEX('a', REVERSE(name))

позволит найти эту позицию, но справа. Для получения позиции этого же символа слева достаточно написать

SELECT LEN(name) + 1 - CHARINDEX('a', REVERSE(name)) first_a

FROM Ships WHERE name='California'

Функция REPLACE

REPLACE ( <строка1 , <строка2 , <строка3 )

Заменяет в строке1 все вхождения строки2 на строку3. Эта функция, безусловно, полезна в операторах обновления (UPDATE), если нужно изменить (исправить) содержимое столбца. Пусть, например, нужно заменить все пробелы дефисом в названиях кораблей. Тогда можно написать

UPDATE Ships

SET name = REPLACE(name, ' ', '-')

(Этот пример можно выполнить на странице с упражнениями DML, где разрешаются запросы на изменение данных)

Однако эта функция может найти применение и в более нетривиальных случаях. Давайте определим, сколько раз в названии корабля используется буква "a". Идея проста: заменим каждую искомую букву двумя любыми символами, после чего посчитаем разность длин полученной и искомой строки. Итак,

SELECT name, LEN(REPLACE(name, 'a', 'aa')) - LEN(name) FROM Ships

А если нам нужно определить число вхождений произвольной последовательности символов, скажем, передаваемой в качестве параметра в хранимую процедуру? Использованный выше алгоритм в этом случае следует дополнить делением на число символов в искомой последовательности:

DECLARE @str AS VARCHAR(100)

SET @str='ma'

SELECT name, (LEN(REPLACE(name, @str, @str + @str)) - LEN(name))/LEN(@str) FROM Ships

Для удвоения числа искомых символов здесь применялась конкатенация - @str + @str . Однако для этой цели можно использовать еще одну функцию - REPLICATE, которая повторяет первый аргумент такое число раз, которое задается вторым аргументом.