Преобразование типов и оператор CAST

В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в Transact-SQL при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int. Подробно о явном и неявном преобразовании типов в SQL Server можно прочитать на справочном ресурсе Microsoft.

Пример 5.9.1

Попытка выполнить запрос

SELECT 'Средняя цена = '+AVG(price)
FROM Laptop;

приведет к сообщению об ошибке:

Это сообщение означает, что система не может выполнить неявное преобразование типа varchar к типу money. В подобных ситуациях может помочь явное преобразование типов. При этом, как указано в сообщении об ошибке, можно воспользоваться функцией CONVERT. Однако эта функция не стандартизована, поэтому в целях переносимости рекомендуется использовать стандартное выражение CAST. С него и начнем.

Итак, если переписать наш запрос в виде

SELECT 'Средняя цена = '+ CAST(AVG(price) AS CHAR(15))
FROM Laptop;

в результате получим то, что требовалось:

Средняя цена = 1003.33

Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению.

Синтаксис выражения CAST очень простой

CAST(<выражение> AS <тип данных>)

Рассмотрим еще один пример.

Пример 5.9.2

Запрос:

SELECT AVG(launched)
FROM Ships;

А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,

SELECT CAST(AVG(launched) AS NUMERIC(6,2))
FROM Ships;

вернет значение 1926.00. Следовательно, CAST нужно применить к аргументу агрегатной функции:

SELECT AVG(CAST(launched AS NUMERIC(6,2)))
FROM Ships;

Результат — 1926.90909. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2))
FROM Ships;

В результате получим то, что нужно — 1926.91. Однако это решение выглядит очень громоздко. Заставим неявное преобразование типа поработать на нас:

SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2))
FROM Ships;

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

Аналогичные преобразования типа можно выполнить с помощью функции SQL Server CONVERT:

SELECT CONVERT(NUMERIC(6,2), AVG(launched*1.0))
FROM Ships;

Функция CONVERT имеет следующий синтаксис:

CONVERT (<тип_данных[(<длина>)]>, <выражение> [, <стиль>])

Основное отличие функции CONVERT от функции CAST состоит в том, что первая позволяет форматировать данные (например, темпоральные данные типа datetime) при преобразовании их к символьному типу и указывать формат при обратном преобразовании. Разные целочисленные значения необязательного аргумента стиль соответствуют различным типам форматов. Рассмотрим следующий пример

SELECT CONVERT(char(25), CONVERT(datetime,'20030722'));

Здесь мы преобразуем строковое представление даты к типу datetime, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано используется значение по умолчанию (0 или 100). В результате получим:

Jul 22 2003 12:00AM

Ниже приведены некоторые другие значения аргумента стиль и результат, полученный на приведенном выше примере. Заметим, что увеличение значения стиль на 100 приводит к четырехзначному отображению года.

Перечень всех возможных значений аргумента стиль можно посмотреть в документации (см. ссылку в начале страницы).

Рекомендуемые упражнения: 32, 35, 53, 54,58, 69, 78, 81, 91, 115, 119

Есть одна особенность использования оператора CAST в SQL Server, связанная с преобразованием числа к его строковому представлению. Что произойдет, если число символов в числе превышает размер строки? Например,

SELECT CAST(1234.6 AS VARCHAR(5));

Следует ожидать, что мы получим сообщение об ошибке. Правильно, вот это сообщение:

(Ошибка арифметического переполнения при преобразовании числа к типу данных VARCHAR.)

Естественно, что мы будем ожидать того же сообщения и при выполнении следующего оператора:

SELECT CAST(123456 AS VARCHAR(5));

Но нет. В результате мы получим символ «*» вместо сообщения об ошибке. Мы не беремся судить, с чем это связано, однако, однажды мы столкнулись с проблемой диагностики ошибки в коде, в котором впоследствии выполнялось обратное преобразование к числовому типу.

В нашем простейшем примере это будет выглядеть так:

SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT);

Вот тут-то мы и получаем ошибку:

(Ошибка синтаксиса при преобразовании значения «*» к типу данных INT.)

Преобразование типа money

Денежный тип данных не является стандартным. В SQL Server имеется два денежных типа:

money: диапазон значений от –922,337,203,685,477.5808 до 922,337,203,685,477.5807

smallmoney: диапазон значений от -214 748,3648 до 214 748,3647

Точность обоих типов одна десятитысячная.

Константу типа money можно задать с помощью префикса $, или же использовать преобразование типов, например:

select 1.2 num, $1.2 mn1, cast(1.2 as MONEY) mn2;

Обратите внимание на запятую в качестве разделителя “рублей” и “копеек” - не точка!

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

select cast(1.75 as INT) int_num, cast($1.75 as INT) int_mon;

Деньги таки, их просто так терять нельзя!