- •Оглавление Извлечение данных. Оператор select
- •Оператор select
- •Предикаты (часть I)
- •Предикаты сравнения
- •Предикат between
- •Предикат in
- •Переименование столбцов и вычисления в результирующем наборе
- •Предикаты (часть 2) Предикат like
- •Использование значения null в условиях поиска Предикат is [not] null
- •Получение итоговых значений
- •Предложение group by
- •Предложение having
- •Использование в запросе нескольких источников записей
- •Явные операции соединения
- •Традиционные операции над множествами и оператор select
- •Декартово произведение
- •Объединение
- •Пересечение и разность
- •Предикат exists
- •Использование ключевых слов some | any и all с предикатами сравнения
- •Еще о подзапросах
- •Преобразование типов
- •Оператор case
- •Функции Transact-sql для обработки даты/времени
- •Функция dateadd
- •Функция datediff
- •Функция datepart
- •Функция datename
- •Функции работы со строками в ms sql server 2005
- •Функция substring
- •Функция reverse
- •Функция replace
- •Функции ltrim и rtrim
- •Функции lower и upper
- •Функция unicode
- •Функция nchar
- •Операторы модификации данных
- •Оператор insert
- •Вставка строк в таблицу, содержащую автоинкрементируемое поле
- •Оператор update
- •Оператор delete
- •Приложение 1. Описание учебных баз данных
- •1. Компьютерная фирма
- •2. Фирма вторсырья
- •3. Корабли
- •4. Аэрофлот
- •5. Окраска
- •Приложение 2. Список упражнений (select)
- •Как объединить данные из двух столбцов в один без использования union и join?
- •1. Union all
- •2. Full join
- •3. Unpivot
- •Комментарии
- •Как добавить новый столбец в таблицу между существующими столбцами?
- •Как вывести по n строк из каждой группы?
- •"Классическое" решение
- •1. Соединение
- •2. Подзапрос в предложении select
- •Решение на основе ранжирующих функций
- •Как удалить дубликаты строк из таблицы?
- •1. Нумерация строк
- •2. Ранжирование строк внутри групп дубликатов
- •3. Удаление дубликатов из виртуальной таблицы
- •Как удалить дубликаты строк при наличии первичного ключа?
- •Id name
- •Id_pk color
- •Id name color
- •Обновление таблицы t_details
- •Id_pk name color dup min_id
- •Id_pk color
- •Id name
- •Id_pk color
Функции работы со строками в ms sql server 2005
Вот полный перечень функций работы со строками, взятый из BOL:
ASCII |
NCHAR |
SOUNDEX |
CHAR |
PATINDEX |
SPACE |
CHARINDEX |
REPLACE |
STR |
DIFFERENCE |
QUOTENAME |
STUFF |
LEFT |
REPLICATE |
SUBSTRING |
LEN |
REVERSE |
UNICODE |
LOWER |
RIGHT |
UPPER |
LTRIM |
RTRIM |
|
Начнем с двух взаимно обратных функций - ASCII и CHAR.
Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.
Вот, например, как можно определить, сколько имеется разных букв, с которых начинаются названия кораблей в таблице Ships:
SELECT COUNT(DISTINCT ASCII(name)) FROM Ships |
Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):
SELECT DISTINCT CHAR(ASCII(name)) FROM Ships ORDER BY 1 |
Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции - LEFT, которая имеет следующий синтаксис:
LEFT (<строковое выражение>, <целочисленное выражение> )
и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,
SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1 |
А вот как, например, можно получить таблицу кодов всех алфавитных символов:
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') |
Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .
Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
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') |
Чтобы таблица выглядела более патриотично, достаточно заменить латинские буквы "a" и "A" на неотличимые на взгляд русские - "а" и "А", а "z" и "Z" на "я" и "Я". Вот только буквы "ё" вы не увидите в этой таблице, т.к. в кодовой таблице ASCII эти символы лежат отдельно, что легко проверить:
SELECT ASCII('ё') UNION ALL SELECT ASCII('Ё') |
Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.
Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX. Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:
CHARINDEX (искомое_выражение, строковое_выражение[, стартовая_позиция])
Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск искомого_выражения . Если этот параметр опущен, поиск выполняется от начала строкового_выражения. Например, запрос
SELECT name FROM Ships WHERE CHARINDEX('sh', name) > 0 |
будет выводить те корабли, в которых имеется сочетание символов "sh". Здесь используется тот факт, что если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие корабли:
name |
Kirishima |
Musashi |
Washington |
Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"
SELECT CHARINDEX('a',name) first_a, CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a FROM Ships WHERE name='California' |
Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX('a', name)+1. Правильность результата - 2 и 10 - легко проверить :-).
Функция PATINDEX имеет синтаксис:
PATINDEX ('%образец%' , строковое_выражение)
Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид
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 возвращает заданное число символов справа из строкового выражения:
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) |
Чтобы исключить этот случай, можно воспользоваться еще одной полезной функцией LEN (<строковое выражение>), которая возвращает число символов в строке. Ограничимся случаем, когда число символов больше единицы:
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 |