лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Cn Iowa Missouri North Carolina Washington А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав: 1. SELECT * 2. FROM ( SELECT class + ' ' + name AS cn 3. FROM Ships 4. UNION ALL 5. SELECT 'a' AS cn 6. ) x 7. WHERE LEFT ( cn, 1 ) = RIGHT ( cn, 1 ) ; Чтобы исключить этот случай, можно воспользоваться еще одной полезной функцией LEN. Функция LEN Функция LEN(строковое выражение) возвращает число символов в строке, задаваемой строковым выражением. Ограничимся случаем, когда число символов больше единицы: 1. SELECT * 2. FROM ( SELECT class + ' ' + name AS cn 3. FROM Ships 4. UNION ALL 5. SELECT 'a' AS nc 6. ) x 7. WHERE LEFT ( cn, 1 ) = RIGHT ( cn, 1 ) AND 8. LEN ( cn ) > 1 ; Реализация функции LEN() в MS SQL Server имеет одну особенность, а именно, она не учитывает концевые пробелы. Давайте выполним следующий код: 1. DECLARE @chr AS CHAR ( 12 ) , @vchr AS VARCHAR ( 12 ) ; 2. SELECT @chr = 'abcde' + REPLICATE ( ' ' , 5 ) , @vchr = 'abcde' +REPLICATE ( ' ' , 5 ) ; 3. SELECT LEN ( @chr ) , LEN ( @vchr ) ; 4. SELECT DATALENGTH ( @chr ) , DATALENGTH ( @vchr ) ; 5 5 12 10 Функция REPLICATE добавляет справа к константе 'abcde' пять пробелов, которые не учитываются функцией LEN, - в обоих случаях мы получаем 5. Функция DATALENGTH возвращает число байтов в строковом представлении и демонстрирует различие в типах данных CHAR и VARCHAR. DATALENGTH возвращает 12 для типа CHAR(12), и 10 - для VARCHAR(12). Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернуло фактическую длину строковой переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR является типом данных фиксированной длины. Если значение переменной меньше объявленной длины, а мы использовали CHAR(12), значение переменной будет дополнено концевыми пробелами, чтобы "выровнять" длину до 12 символов. На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т. д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете (2d) или скорость привода CD-ROM (24x). Проблема заключается в том, что текст сортируется так (по возрастанию): 11a 1a 2a Действительно, 1. SELECT '1a' AS place 2. UNION ALL 3. SELECT '2a' 4. UNION ALL 5. SELECT '11a' 6. ORDER BY 1 ; Если же требуется упорядочить места в самолете в порядке следования рядов, то порядок должен быть таким: 1a 2a 11a Чтобы добиться такого порядка, нужно выполнить сортировку по числовым значениям, присутствующим в тексте. Можно использовать такой алгоритм: 1. Извлечь число из строки. 2. Привести его к числовому формату. 3. Выполнить сортировку по приведенному значению. Так как нам известно, что буква только одна, то для извлечения числа из строки можно воспользоваться следующей конструкцией, которая не зависит от числа цифр в номере места: 1. LEFT ( place, LEN ( place ) - 1 ) Если только этим и ограничиться, то получим place 1a 11a 2a Приведение к числовому формату может быть следующим: 1. CAST ( LEFT ( place, LEN ( place ) - 1 ) AS INT ) Осталось выполнить сортировку: 1. SELECT * 2. FROM ( SELECT '1a' AS place 3. UNION ALL 4. SELECT '2a' 5. UNION ALL 6. SELECT '11a' 7. ) x 8. ORDER BY CAST ( LEFT ( place, LEN ( place ) - 1 ) AS INT ) ; Что и требовалось доказать. Ранее мы для извлечения числа из текстовой строки пользовались функцией LEFT, так как нам было известно априори, какое число символов нужно убрать справа (один). А если же нужно извлечь строку из подстроки не по известной позиции символа, а по самому символу? Например: извлечь все символы до первой буквы х (значение скорости привода CD-ROM). В этом случае мы можем использовать также уже рассмотренную ранее функцию CHARINDEX, которая позволит определить неизвестную позицию символа: 1. SELECT model, LEFT ( cd, CHARINDEX ( 'x' , cd ) -1 ) 2. FROM PC; Функция LEN(), концевые пробелы и уникальность Недавно я столкнулся с тем, что не смог добавить два значения типа VARCHAR, отличающиеся только концевым пробелом в столбец составного первичного ключа (SQL Server 2008). Возможно, этот факт для кого-то является очевидным, но мне показалось странным, что в принципе разные значения считаются дубликатами. Со значениями типа CHAR(n), который имеет фиксированную длину, все понятно, т.к. короткие строки дополняются пробелами до длины n. Поэтому вводимые значения, которые отличаются лишь концевыми пробелами, оказываются неразличимыми. Но концевой пробел в значении типа VARCHAR является как бы обычным символом. Вот простой эксперимент. 1. CREATE TABLE Test_Trailing_Space2 2. ( num int NOT NULL , name VARCHAR ( 10 ) NOT NULL , 3. PRIMARY KEY ( num, name ) ) ; 4. GO 5. INSERT INTO Test_Trailing_Space2 VALUES ( 1 , 'John' ) ; 6. INSERT INTO Test_Trailing_Space2 VALUES ( 1 , 'John ' ) ; 7. GO Вторая строка не будет вставлена в таблицу, при этом будет получено сообщение о нарушении ограничения первичного ключа. Т.е. вторая строка считается дубликатом первой. Может быть дело в том, что при вставке концевой пробел был отсечен. Но нет, вставим другую уникальную строку с концевым пробелом и проверим наличие в ней концевого пробела: 1. INSERT INTO Test_Trailing_Space2 VALUES ( 2 , 'John ' ) ; 2. GO 3. SELECT *, LEN ( name ) len1,DATALENGTH ( name ) len2 4. FROM Test_Trailing_Space2; Результат: num name len1 len2 1 John 4 4 2 John 4 5 Значение в столбце len2 показывает, что пробел присутствует в данных, но, тем не менее, значения 'John' и 'John ' считаются дубликатами: 1. SELECT DISTINCT name 2. FROM Test_Trailing_Space2; name John Очевидно, что все дело в функции LEN(), которая, как известно, не учитывает концевые пробелы. Я не нашел этой информации в BOL, но, видимо, именно эта функция используется при сравнении значений любых строковых типов. Мне стало интересно, как ведут себя другие СУБД в этом случае, и я повторил эксперимент для MySQL и PostgreSQL. Были получены следующие результаты. MySQL (версия 5.0) 1. SELECT *, OCTET_LENGTH ( name ) AS len1, LENGTH ( name ) AS len2 2. FROM Test_Trailing_Space2; 1 John 4 4 2 John 5 5 1. SELECT DISTINCT name FROM Test_Trailing_Space2; John PostgreSQL (версия 8.3.6) 1. SELECT *, OCTET_LENGTH ( name ) AS len1, LENGTH ( name ) AS len2 2. FROM Test_Trailing_Space2; 1 "John" 4 4 2 "John " 5 5 1. SELECT DISTINCT name 2. FROM Test_Trailing_Space2; "John" "John " Как видно, и MySQL, и PostgreSQL учитывают пробел как в числе символов, так и в числе байтов, используемых для хранения строкового значения. При этом MySQL и SQL Server, в отличие от PostgreSQL, считают строки, различающиеся лишь концевыми пробелами, дубликатами. Естественно, PostgreSQL позволяет вставить и такую строку в рассматриваемую таблицу: 1. INSERT INTO Test_Trailing_Space2 VALUES ( 1 , 'John ' ) ; Вместо выводов Я далек от того, чтобы высказываться относительно правильности той или иной реализации и, тем более, спорить о том, какая СУБД лучше. Я считаю, что нужно знать досконально ту СУБД, которую вы используете в своей профессиональной деятельности. Изучайте документацию и все подвергайте проверке, не полагаясь на интуицию и «здравый» смысл. Функция SUBSTRING Функция SUBSTRING(выражение, начальная позиция, длина) позволяет извлечь из выражения его часть заданной длины, начиная от заданной начальной позиции. Выражение может быть символьной или бинарной строкой, а также иметь тип text или image. Например, если нам потребуется получить три символа в названии корабля, начиная со второго символа, то сделать это без помощи функции SUBSTRING будет не просто. А так мы напишем: 1. SELECT name, SUBSTRING ( name, 2 , 3 ) 2. FROM Ships; В случае, когда нужно извлечь все символы, начиная с заданного, мы также можем применить эту функцию. Например, 1. SELECT name, SUBSTRING ( name, 2 , LEN ( name )) 2. FROM Ships; даст нам все символы в названиях кораблей от второй буквы в имени. Обратите внимание на то, что для указания числа извлекаемых символов мы использовали функцию LEN(name), которая возвращает число символов в имени. Понятно, что поскольку нам нужны символы, начиная со второго, то их число будет меньше общего количества символов в имени. Однако это не вызывает ошибки, поскольку если указанное число символов превышает возможное число, то будут извлечены все символы до конца строки. Поэтому мы берем их с запасом, не утруждая себя вычислениями. Функция REVERSE Эта функция переворачивает строку, как бы читая ее справа налево. То есть результатом запроса 1. SELECT REVERSE ( 'abcdef' ) ; будет fedcba. Если бы отсутствовала функция RIGHT, то запрос 1. SELECT RIGHT ( 'abcdef' , 3 ) ; можно было бы равносильно заменить запросом 1. SELECT REVERSE ( LEFT ( REVERSE ( 'abcdef' ) , 3 )) ; Польза этой функции в следующем. Пусть нам требуется определить позицию не первого, а последнего вхождения некоторого символа (или последовательности символов) в строке. Вспомним пример, в котором мы определяли позицию первого символа а в названии корабля California: 1. SELECT CHARINDEX ( 'a' , name ) first_a 2. FROM Ships 3. WHERE name = 'California' ; Определим теперь позицию последнего вхождения в это название символа а. Функция CHARINDEX('a', REVERSE(name)) позволит найти эту позицию, но справа. Для получения позиции этого же символа слева достаточно написать 1. SELECT LEN ( name ) + 1 - CHARINDEX ( 'a' , REVERSE ( name )) first_a 2. FROM Ships 3. WHERE name = 'California' ; Функция REPLACE Функция 1. REPLACE ( строка 1 , строка 2 , строка 3 ) заменяет в строке1 все вхождения строки2 на строку3. Эта функция, безусловно, полезна в операторах обновления (UPDATE), если нужно изменить (исправить) содержимое столбца. Пусть, например, нужно заменить все пробелы дефисом в названиях кораблей. Тогда можно написать: 1. UPDATE Ships 2. SET name = REPLACE ( name, ' ' , '-' ) ; Примечание: Этот пример можно выполнить на странице с упражнениями DML , где разрешаются запросы на изменение данных. Однако эту функцию можно применять и в более нетривиальных случаях. Давайте определим, сколько раз в названии корабля используется буква «a». Идея проста: заменим каждую искомую букву двумя любыми символами, после чего посчитаем разность длин полученной и искомой строки. Итак, 1. SELECT name, LEN ( REPLACE ( name, 'a' , 'aa' )) - LEN ( name ) 2. FROM Ships; А если нам нужно определить число вхождений произвольной последовательности символов, скажем, передаваемой в качестве параметра в хранимую процедуру? Примененный выше алгоритм в этом случае следует дополнить делением на число символов в искомой последовательности: 1. DECLARE @str AS VARCHAR ( 100 ) ; 2. SET @str= 'ma' ; 3. SELECT name, ( LEN ( REPLACE ( name, @str, @str + @str )) - LEN ( name )) /LEN ( @str ) FROM Ships; Следует быть весьма осторожным с применением данного метода к нахождению числа пробелов. Помимо того, что функция LEN не учитывает концевые пробелы, результат будет зависеть от типа данных. В строке `World Wide Web` 14 символов. Заменим теперь каждый пробел двухсимвольной строкой, и подсчитаем длину до замены и после: 1. declare @w char ( 50 ) = 'World Wide Web' ; 2. SELECT len ( REPLACE ( @w, ' ' , 'xx' )) after, len ( @w ) before; after before 88 14 Мы хотели добавить 2 символа, а получили 88 вместо 16. Дело в том, что тип CHAR является точным типом, а это означает, что длина любой строки этого типа будет иметь указанный размер. Мы задали 50 символов, следовательно, строковое значение будет дополняться концевыми пробелами для выравнивания его до 50 символов, Итого, дополнительно получим (50-14)х2 = 72, и всего 72 + 16 = 88. Вот если бы мы описали переменную типом VARCHAR(50), то получили желаемый результат: after before 16 14 Функции REPLICATE и STUFF Для удвоения числа искомых символов здесь применялась конкатенация — @str+@str. Однако для этой цели можно использовать еще одну функцию — REPLICATE, которая повторяет первый аргумент такое число раз, которое задается вторым аргументом. 1. SELECT name, ( LEN ( REPLACE ( name, @str, REPLICATE ( @str, 2 ))) — 2. LEN ( name )) /LEN ( @str ) 3. FROM Ships; То есть мы повторяем дважды подстроку, хранящуюся в переменной @str. Если же нужно заменить в строке не определенную последовательность символов, а заданное число символов, начиная с некоторой позиции, то проще выбрать функцию STUFF: 1. STUFF ( строка 1 , стартовая позиция, L , строка 2 ) Эта функция заменяет подстроку длиной L, которая начинается со стартовой позиции в строке1 на строку2. Пример 7.2.1 Изменить имя корабля: оставив в его имени 5 первых символов, дописать «_» (нижнее подчеркивание) и год спуска на воду. Если в имени менее 5 символов, дополнить его пробелами. Можно решать эту задачу с помощью разных функций. Мы же попытаемся это сделать с помощью функции STUFF. В первом приближении напишем (ограничимся запросом на выборку): 1. SELECT name, STUFF ( name, 6 , LEN ( name ) , '_' +launched ) 2. FROM Ships; В качестве третьего аргумента (количества символов для замены) мы используем LEN(name) , так как нам нужно заменить все символы до конца строки, поэтому мы берем с запасом — исходное число символов в имени. И все же этот запрос вернет ошибку. Причем дело не в третьем аргументе, а в четвертом, где выполняется конкатенация строковой константы и числового столбца. Ошибка приведения типа. Для преобразования числа к его строковому представлению можно воспользоваться еще одной встроенной функцией — STR Параметр L функции STUFF целочисленный; это значит, что он может принимать отрицательные значения и 0. Для отрицательных значений функция STUFF вернет NULL, как и для случая, когда второй параметр превышает длину строки1. Нуль же означает вставку строки2 в строку1, начиная с позиции, заданной вторым параметром. Пример 7.2.2 Добавить разделитель "-" в строковое представление даты в формате YYYYMMDD 1. SELECT STUFF ( STUFF ( '20121119' , 5 , 0 , '-' ) , 8 , 0 , '-' ) ; Функции STR, SPACE, LTRIM и RTRIM Функция STR() преобразует число к его символьному представлению: 1. STR ( число с плавающей точкой [ , длина [ , число десятичных знаков ] ] ) При этом преобразовании выполняется округление, а длина задает длину результирующей строки. Например, STR(3.3456, 5, 1) 3.3 STR(3.3456, 5, 2) 3.35 STR(3.3456, 5, 3) 3.346 STR(3.3456, 5, 4) 3.346 Обратите внимание, что если полученное строковое представление числа меньше заданной длины, то добавляются лидирующие пробелы. Если же результат больше заданной длины, то усекается дробная часть (с округлением); в случае же целого числа получаем соответствующее число звездочек «*»: 1. STR ( 12345 , 4 , 0 ) **** Кстати, по умолчанию используется длина в 10 символов. Имея в виду, что год представлен четырьмя цифрами, напишем: 1. SELECT name, STUFF ( name, 6 , LEN ( name ) , '_' +STR ( launched, 4 )) 2. FROM Ships; Уже почти все правильно. Осталось учесть случай, когда число символов в имени менее 6, так как в этом случае функция |