>SELECT name FROM Ships WHERE PATINDEX('%sh%', name) 0
А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть "e":
>SELECT name FROM Ships WHERE PATINDEX('%e_e%', name) 0
Результат выполнения этого запроса выглядит следующим образом:
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)
Функция REPLICATE дополняет константу 'abcde' пятью пробелами справа, которые не учитываются функцией LEN, - в обоих случаях получаем 5.
Функция DATALENGTH возвращает число байтов в представлении переменной и демонстрирует нам различие между типами CHAR и VARCHAR. DATALENGTH даст нам 12 для типа CHAR и 10 - для VARCHAR.
Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернула фактическую длину переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR - это тип фиксированной длины. Если значение переменной оказывается меньше ее длины, а длину мы объявили как CHAR(12), то значение переменной будет "выровнено" до требуемой длины за счет добавления концевых пробелов.
На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т.д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете ("2d") или скорость CD ("24x"). Проблема заключается в том, что текст сортируется так (по возрастанию)
Действительно,
>SELECT '1a' AS place
>UNION ALL SELECT '2a'
>UNION ALL SELECT '11a'
>ORDER BY 1
Если же требуется упорядочить места в порядке возрастания рядов, то порядок должен быть такой
Чтобы добиться такого порядка, нужно выполнить сортировку по числовым значениям, присутствующим в тексте. Можно предложить такой алгоритм:
1. Извлечь число из строки.
2. Привести его к числовому формату.
3. Выполнить сортировку по приведенному значению.
Т.к. нам известно, что буква только одна, то для извлечения числа из строки можно воспользоваться следующей конструкцией, которая не зависит от числа цифр в номере места:
>LEFT(place, LEN(place)-1)
Если только этим и ограничиться, то получим
Приведение к числовому формату может быть следующим:
>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)
