Читаем без скачивания Справка по SQL - W Cat

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

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

Т.е. мы повторяем дважды подстроку, хранящуюся в переменной @str .

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

Эта функция заменяет подстроку длиной L, которая начинается со стартовой позиции в строке1, на строку2.

Пример. Изменить имя корабля: оставив в его имени 5 первых символов, дописать "_" (нижнее подчеркивание) и год спуска на воду. Если в имени менее 5 символов, дополнить его пробелами.

Можно решать эту задачу с помощью разных функций. Мы же попытаемся это сделать с помощью функции STUFF. В первом приближении напишем (ограничимся запросом на выборку):

SELECT name, STUFF(name, 6, LEN(name), '_'+launched) FROM Ships

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

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

Обратите внимание, что если полученное строковое представление числа меньше заданной длины, то добавляются лидирующие пробелы. Если же результат больше заданной длины, то усекается дробная часть (с округлением); в случае же целого числа получаем соответствующее число звездочек "*":

STR(12345,4,0)

****

Кстати, по умолчанию используется длина в 10 символов. Имея в виду, что год представлен четырьмя цифрами, напишем

SELECT name, STUFF(name, 6, LEN(name), '_'+STR(launched, 4)) FROM Ships

Уже почти все правильно. Осталось учесть случай, когда число символов в имени менее 6, т.к. в этом случае функция STUFF дает NULL. Ну что ж вытерпим до конца мучения, связанные с использованием этой функции в данном примере, попутно применив еще одну строковую функцию. Добавим конечные пробелы, чтобы длина имени была заведомо больше 6. Для этого имеется специальная функция SPACE :

SELECT name, STUFF(name + SPACE(6), 6, LEN(name), '_'+STR(launched,4)) FROM Ships

Функции LTRIM и RTRIM

LTRIM (<строковое выражение)

RTRIM (<строковое выражение)

отсекают соответственно лидирующие и конечные пробелы строкового выражения, которое неявно приводится к типу VARCHAR.

Пусть требуется построить такую строку: имя пассажира_идентификатор пассажира для каждой записи из таблицы Passenger. Если мы напишем

SELECT name + '_' + CAST(id_psg AS VARCHAR) FROM Passenger,

то в результате получим что-то типа:

A _1

Это связано с тем, что столбец name имеет тип CHAR(30). Для этого типа короткая строка дополняется пробелами до заданного размера (у нас 30 символов). Здесь нам как раз и поможет функция RTRIM:

SELECT RTRIM(name) + '_' + CAST(id_psg AS VARCHAR) FROM Passenger

Функции LOWER и UPPER

LOWER(<строковое выражение)

преобразуют все символы аргумента соответственно к нижнему и верхнему регистру. Эти функции оказываются полезными при сравнении регистрозависимых строк.

Пара интересных функций SOUNDEX и DIFFERENCE:

SOUNDEX(<строковое выражение)

DIFFERENCE (<строковое выражение_1, <строковое выражение_2)

Позволяют определить близость звучания слов. При этом SOUNDEX возвращает четырехсимвольный код, используемый для сравнения, а DIFFERENCE собственно и оценивает близость звучания двух сравниваемых строковых выражений. Поскольку эти функции не поддерживают кириллицы, отсылаю интересующихся к BOL за примерами их использования.

В заключение приведем функции и несколько примеров использования юникода.

Функция UNICODE

UNICODE (<строковое выражение)

возвращает юникод первого символа строкового выражения.

Функция NCHAR

NCHAR (<целое)

возвращает символ по его юникоду. Несколько примеров.

SELECT ASCII('а'), UNICODE('а')

возвращает код ASCII и юникод русской буквы "а": 224 и 1072.

SELECT CHAR(ASCII('а')), CHAR(UNICODE('а'))

Пытаемся восстановить символ по его коду. Получаем "а" и NULL. NULL-значение возвращается потому, что кода 1072 нет в обычной кодовой таблице.

SELECT CHAR(ASCII('а')), NCHAR(UNICODE('а'))

Теперь все нормально, в обоих случаях "а". Наконец,

SELECT NCHAR(ASCII('а'))

даст "a", т.к. юникод 224 соответствует именно этой букве.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Операторы модификации данных

Язык манипуляции данными (DML - Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются:

INSERT Добавление записей (строк) в таблицу БД

UPDATE Обновление данных в столбце таблицы БД

DELETE Удаление записей из таблицы БД

Оператор INSERT

Оператор INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой литеральные константы либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.

Синтаксис оператора

INSERT INTO <имя таблицы[(<имя столбца,...)]

{VALUES (< значение столбца,…)}

| <выражение запроса

| {DEFAULT VALUES};

Как видно из представленного синтаксиса, список столбцов не является обязательным. В том случае, если он отсутствует, список вставляемых значений должен быть полный, т.е. обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку столбцов, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, каждое из этих значений должно быть того же типа (или приводиться к нему), что и тип, определенный для соответствующего столбца в операторе CREATE TABLE. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE:

CREATE TABLE [dbo].[product] (

[maker] [char] (1) NOT NULL ,

[model] [varchar] (4) NOT NULL ,

[type] [varchar] (7) NOT NULL )

Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:

INSERT INTO Product VALUES ('B', 1157, 'PC');

Если задать список столбцов, то можно изменить "естественный" порядок их следования:

INSERT INTO Product (type, model, maker) VALUES ('PC', 1157, 'B');

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

CREATE TABLE [product_D] (

[maker] [char] (1) NULL ,

[model] [varchar] (4) NULL ,

[type] [varchar] (7) NOT NULL DEFAULT 'PC' )

Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два - NULL, а последний столбец - type - 'PC'). Теперь мы могли бы написать:

INSERT INTO Product_D (model, maker) VALUES (1157, 'B');

В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию - 'PC'. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL.

Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT: