Преобразование типов данных (ядро СУБД)
Преобразование типов данных происходит в следующих случаях:
- При перемещении, сравнении или объединении данных одного объекта с данными другого объекта эти данные могут преобразовываться из одного типа в другой.
- При передаче в переменную программы данных из результирующего столбца Transact-SQL, кода возврата или параметра вывода эти данные должны преобразовываться из системного типа данных SQL Server в тип данных переменной.
При преобразовании между переменной приложения и столбцом результирующих наборов SQL Server, возвращаемым кодом, параметром или маркером параметров поддерживаемые преобразования типов данных определяются API базы данных.
Явное и неявное преобразование
Преобразование типов данных бывает явным и неявным.
Неявное преобразование скрыто от пользователя. SQL Server автоматически преобразует данные из одного типа данных в другой. Например, если smallint сравнивается с int, то перед сравнением smallint неявно преобразуется в int.
GETDATE() выполняет неявное преобразование в стиль даты 0. SYSDATETIME() выполняет неявное преобразование в стиль даты 21.
Явное преобразование выполняется с помощью функций CAST и CONVERT.
Функции CAST и CONVERT преобразуют значение (локальную переменную, столбец или выражение) из одного типа данных в другой. Например, приведенная ниже функция CAST преобразует числовое значение $157.27 в строку символов ‘157.27’ :
CAST ( $157.27 AS VARCHAR(10) ) Если программный код Transact-SQL должен соответствовать требованиям ISO, используйте функцию CAST вместо CONVERT. Использование функции CONVERT вместо CAST дает преимущество в дополнительной функциональности.
На следующем рисунке показаны все явные и неявные преобразования типов данных, которые разрешены для системных типов данных SQL Server. Это могут быть типы xml, bigint и sql_variant. При присваивании неявного преобразования из типа sql_variant не происходит, но неявное преобразование в тип sql_variant производится.
Хотя на приведенной выше диаграмме показаны все явные и неявные преобразования, которые допускаются в SQL Server, в ней не указан результирующий тип данных. Когда SQL Server выполняет явное преобразование, сам оператор определяет результирующий тип данных. Для неявных преобразований операторы назначения, такие как установка значения переменной или вставка значения в столбец, дают в результате тип данных, определенный в объявлении переменной или в определении столбца. Для операторов сравнения или других выражений результирующий тип данных зависит от правил приоритета типов данных.
Например, следующий сценарий определяет переменную типа varchar , присваивает переменной значение типа int , а затем выбирает объединение переменной со строкой.
DECLARE @string VARCHAR(10); SET @string = 1; SELECT @string + ' is a string.' Значение int 1 преобразуется в varchar , поэтому оператор SELECT возвращает значение 1 is a string. .
В следующем примере показан похожий сценарий с переменной int :
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + ' is not a string.' В этом случае оператор SELECT выдает следующую ошибку:
Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ‘ is not a string.’ to data type int.
Чтобы вычислить выражение @notastring + ‘ is not a string.’ , SQL Server следует правилам приоритета типов данных для выполнения неявного преобразования перед вычислением результата выражения. Поскольку int имеет более высокий приоритет, чем varchar , SQL Server пытается преобразовать строку в целое число, и операция завершается ошибкой, так как эта строка не может быть преобразована в целое число. Если выражение содержит строку, которую можно преобразовать, работа оператора завершается успешно, как показано в следующем примере:
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + '1' В этом случае строка 1 может быть преобразована в целочисленное значение 1 , поэтому оператор SELECT возвращает значение 2 . Обратите внимание, что оператор + выполняет сложение, а не объединение, если предоставленные типы данных являются целыми числами.
Поведение преобразования типов данных
Некоторые неявные и явные преобразования типов данных не поддерживаются при преобразовании типа данных одного объекта SQL Server в другой. Например, значение типа nchar нельзя преобразовать в значение типа image. Тип данных nchar можно преобразовать в тип данных binary только явно. Неявное преобразование в binary не поддерживается. Однако тип данных nchar можно преобразовать в тип nvarchar как явно, так и неявно.
В следующих темах приведено описание процесса преобразования следующих типов данных:
- binary и varbinary (Transact-SQL)
- datetime2 (Transact-SQL)
- money and smallmoney (Transact-SQL)
- bit (Transact-SQL)
- datetimeoffset (Transact-SQL)
- smalldatetime (Transact-SQL)
- char и varchar (Transact-SQL)
- десятичная и числовая (Transact-SQL)
- sql_variant (Transact-SQL)
- date (Transact-SQL)
- float и real (Transact-SQL)
- time (Transact-SQL)
- datetime (Transact-SQL)
- int, bigint, smallint и tinyint (Transact-SQL)
- uniqueidentifier (Transact-SQL)
Преобразование типов данных с помощью хранимых процедур OLE-автоматизации
Поскольку SQL Server использует типы данных Transact-SQL, а служба автоматизации OLE — типы данных Visual Basic, хранимым процедурам службы автоматизации OLE приходится преобразовывать данные, которыми они обмениваются.
В следующей таблице описаны преобразования типов данных SQL Server в Visual Basic.
| Тип данных SQL Server | Тип данных Visual Basic |
|---|---|
| char, varchar, text, nvarchar, ntext | String |
| decimal, numeric | String |
| bit | Boolean |
| binary, varbinary, image | Одномерный массив Byte() |
| int | Long |
| smallint | Целое число |
| tinyint | Byte |
| float | Двойной |
| real | Один |
| money, smallmoney | Валюта |
| datetime, smalldatetime | Дата |
| Все значения NULL | Variant со значением NULL |
Все значения SQL Server преобразуются в одно значение Visual Basic, за исключением двоичных , varbinary и изображений. Эти значения преобразуются в одномерный массив Byte() в Visual Basic. Этот массив имеет диапазон байт ( от 0 до длины 1**)**, где длина — количество байтов в значениях binary, varbinary или image SQL Server.
Это преобразования типов данных Visual Basic в типы данных SQL Server.
| Тип данных Visual Basic | Тип данных SQL Server |
|---|---|
| Long, Integer, Byte, Boolean, Object | int |
| Double, Single | float |
| Валюта | money |
| Дата | datetime |
| String длиной 4000 символов или меньше | varchar/nvarchar |
| String длиной более 4000 символов | text/ntext |
| Одномерный массив Byte() размером 8000 байт или меньше | varbinary |
| Одномерный массив Byte() размером более 8000 байт | Изображение |
Как перевести string в int sql
Продвинутый SQL
Когда мы выполняем операции со значениями с разными типами данных, СУБД пытается выполнить преобразование и привести используемые значения к нужному типу. Например, в примере ниже мы сравниваем значения с типами STRING и INT . Чтобы выполнить это сравнение MySQL автоматически сконвертирует строковое значение в числовое.
MySQLSELECT '50' > 49, '50' > 51;
'50' > 49 '50' > 51 1 0 Но не все преобразования СУБД может сделать автоматически, и тогда необходимо делать явное преобразование типов. Для этого в MySQL есть две очень схожие функции CAST и CONVERT .
MySQLCAST(значение AS тип_для_конвертации); CONVERT(значение, тип_для_конвертации);MySQLSELECT CAST(12005.6 AS DECIMAL), CONVERT(12005.4, DECIMAL);
CAST(12005.6 AS DECIMAL) CONVERT(12005.4, DECIMAL) 12006 12005 Как перевести string в int sql
Когда мы присваиваем значение одного типа столбцу, который хранит данные другого типа, либо выполняем операции, которые вовлекают данные разных типов, SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически. SQL Server может выполнять неявные преобразования от типа с меньшим приоритетом к типу с большим приоритетом. Таблица приоритетов (чем выше, тем больший приоритет):
datetime smalldatetime float real decimal money smallmoney int smallint tinyint bit nvarchar nchar varchar char То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и время (datetime).
В тех случаях, когда необходимо выполнить преобразования от типов с высшим приоритетом к типам с низшим приоритетом, то надо выполнять явное приведение типов. Для этого в T-SQL определены две функции: CONVERT и CAST .
Функция CAST преобразует выражение одного типа к другому. Она имеет следующую форму:
CAST(выражение AS тип_данных)Для примера возьмем следующие таблицы:
CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL ); CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );Например, при выводе информации о заказах преобразует числовое значение и дату в строку:
SELECT Id, CAST(CreatedAt AS nvarchar) + '; total: ' + CAST(Price * ProductCount AS nvarchar) FROM Orders
Convert
Большую часть преобразований охватывает функция CAST. Если же необходимо какое-то дополнительное форматирование, то можно использовать функцию CONVERT . Она имеет следующую форму:
CONVERT(тип_данных, выражение [, стиль])Третий необязательный параметр задает стиль форматирования данных. Этот параметр представляет числовое значение, которое для разных типов данных имеет разную интерпретацию. Например, некоторые значения для форматирования дат и времени:
- 0 или 100 - формат даты "Mon dd yyyy hh:miAM/PM" (значение по умолчанию)
- 1 или 101 - формат даты "mm/dd/yyyy"
- 3 или 103 - формат даты "dd/mm/yyyy"
- 7 или 107 - формат даты "Mon dd, yyyy hh:miAM/PM"
- 8 или 108 - формат даты "hh:mi:ss"
- 10 или 110 - формат даты "mm-dd-yyyy"
- 14 или 114 - формат даты "hh:mi:ss:mmmm" (24-часовой формат времени)
Некоторые значения для форматирования данных типа money в строку:
- 0 - в дробной части числа остаются только две цифры (по умолчанию)
- 1 - в дробной части числа остаются только две цифры, а для разделения разрядов применяется запятая
- 2 - в дробной части числа остаются только четыре цифры
Например, выведем дату и стоимость заказов с форматированием:
SELECT CONVERT(nvarchar, CreatedAt, 3), CONVERT(nvarchar, Price * ProductCount, 1) FROM Orders
TRY_CONVERT
При использовании функций CAST и CONVERT SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:
SELECT CONVERT(int, 'sql')Чтобы избежать генерации исключения можно использовать функцию TRY_CONVERT . Ее использование аналогично функции CONVERT за тем исключением, что если выражение не удается преобразовать к нужному типу, то функция возвращает NULL:
SELECT TRY_CONVERT(int, 'sql') -- NULL SELECT TRY_CONVERT(int, '22') -- 22Дополнительные функции
Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут использоваться для преобразования в ряд типов:
- STR(float [, length [,decimal]]) : преобразует число в строку. Второй параметр указывает на длину строки, а третий - сколько знаков в дробной части числа надо оставлять
- CHAR(int) : преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
- ASCII(char) : преобразует символ в числовой код ASCII
- NCHAR(int) : преобразует числовой код UNICODE в символ
- UNICODE(char) : преобразует символ в числовой код UNICODE
SELECT STR(123.4567, 6,2) -- 123.46 SELECT CHAR(219) -- Ы SELECT ASCII('Ы') -- 219 SELECT NCHAR(1067) -- Ы SELECT UNICODE('Ы') -- 1067Преобразование типов
В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в T-SQL при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int. Подробно о явном и неявном преобразовании типов в MS SQL Server можно прочитать в BOL.
приведет к сообщению об ошибке
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Это сообщение означает, что система не может выполнить неявное преобразование типа varchar к типу money. В подобных ситуациях может помочь явное преобразование типов. При этом, как указано в сообщении об ошибке, можно воспользоваться функцией CONVERT. Однако эта функция не стандартизована, поэтому в целях переносимости рекомендуется использовать стандартное выражение CAST. С него и начнем.
Если переписать наш запрос в видев результате получим то, что требовалось:
Средняя цена = 1410.44 Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению. Синтаксис выражения CAST очень простой:
При этом следует иметь в виду, во-первых, что не любые преобразования типов возможны (стандарт содержит таблицу допустимых преобразований типов данных). Во-вторых, результат функции CAST для значения выражения, равного NULL, тоже будет NULL.
Рассмотрим еще один пример: определить средний год спуска на воду кораблей из таблицы Ships. Запросдаст результат 1926. В принципе все правильно, т.к. мы получили в результате то, что просили - ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут следует отметить, что агрегатные функции (за исключением функции COUNT, которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched - целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте - не округленное).
А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,вернет значение 1926.00. Следовательно, CAST нужно применить к аргументу агрегатной функции:
Результат - 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:
В результате получим то, что нужно - 1926.24. Однако это решение выглядит очень громоздко. Заставим неявное преобразование типа поработать на нас:
Т.е. мы использовали неявное преобразование целочисленного аргумента к точному числовому типу (EXACT NUMERIC), умножив его на вещественную единицу, после чего применили явное приведения типа результата агрегатной функции.
Аналогичные преобразования типа можно выполнить с помощью функции CONVERT:
Функция CONVERT имеет следующий синтаксис:
Основное отличие функции CONVERT от функции CAST состоит в том, что первая позволяет форматировать данные (например, темпоральные данные типа datetime) при преобразовании их к символьному типу и указывать формат при обратном преобразовании. Разные целочисленные значения необязательного аргумента стиль соответствуют определенным форматам. Рассмотрим следующий пример
Здесь мы преобразуем строковое представление даты к типу datetime, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано, используется значение по умолчанию (0 или 100). В результате получим
Jul 22 2003 12:00AM Ниже приведены некоторые другие значения аргумента стиль и результат, полученный на приведенном выше примере. Заметим, что значения стиль большие 100 приводят к четырехзначному отображению года.
стиль формат 1 07/22/03 11 03/07/22 3 22/07/03 121 2003-07-22 00:00:00.000
Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.Оператор CASE
Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (нет в таблице РС), то вместо цены вывести текст: "Нет в наличии".
Список всех моделей ПК с ценами можно получить с помощью запроса:В результирующем наборе отсутствующая цена будет заменена NULL-значением:
model price 1121 850 1232 350 1232 400 1232 600 1233 600 1233 950 1233 980 1260 350 2111 NULL 2112 NULL
Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE:Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст "Нет в наличии", в противном случае (ELSE) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим
model price 1121 850 1232 350 1232 400 1232 600 1233 600 1233 950 1233 980 1260 350 2111 Нет в наличии 2112 Нет в наличии Оператор CASE может быть использован в одной из двух синтаксических форм записи:
Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.
В приведенном выше примере была использована вторая форма оператора CASE.
Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора - COALESCE. Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE:CASE WHEN A IS NOT NULL THEN A ELSE B END
Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:
Использование первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере: Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели.
В результате выполнения запроса получим
model price comment 1232 350 Самый дешевый 1260 350 Самый дешевый 1232 400 Средняя цена 1233 400 Средняя цена 1233 600 Средняя цена 1121 850 Средняя цена 1233 950 Средняя цена 1233 980 Самый дорогой
