Функции CAST и CONVERT (Transact-SQL)

Изменения: 17 июля 2006 г.

Явно приводят выражение одного типа данных к другому типу. Функции CAST и CONVERT предоставляют схожие возможности.

Синтаксические обозначения в Transact-SQL

Синтаксис

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Аргументы

• expression
  Любое допустимое выражение.

• data_type
  Целевой тип данных, предоставляемый системой. К таким типам данных относятся xml, bigint и sql_variant. Псевдонимы типов данных недопустимы. Дополнительные сведения о доступных типах данных см. в разделе Типы данных (Transact-SQL).

• length
  Необязательный параметр для типов данных nchar, nvarchar, char, varchar, binary и varbinary. Если в функции CONVERT аргумент length не указан, его значение по умолчанию — 30 символов.

• style
  Стиль формата даты, используемого для преобразования типов datetime и smalldatetime в символьные данные (типов nchar, nvarchar, char, varchar, nchar или nvarchar), либо для преобразования символьных данных известных форматов даты и времени в данные типов datetime и smalldatetime; либо строковый формат, используемый для преобразования данных типов float, real, money и smallmoney в символьные данные (типов nchar, nvarchar, char, varchar, nchar и nvarchar). Если аргумент style имеет значение NULL, возвращаемый результат также будет NULL.

  Примечание.

  Стили, перечисленные в этом разделе, поддерживаются SQL Server в описанных сочетаниях с целевыми типами данных функции CONVERT. Все другие стили и сочетания не поддерживаются. Не пользуйтесь неподдерживаемыми стилями. При использовании неподдерживаемых стилей или сочетаний стилей и целевых типов данных можно получить ошибку или ненадежные результаты. Воспроизводимость результатов в других выпусках SQL Server не гарантируется.

  SQL Server поддерживает формат даты в арабском стиле, используя кувейтский алгоритм.

  В следующей таблице два столбца слева представляют значения аргумента style для преобразования datetime или smalldatetime в символьные данные. Добавьте 100 к значению аргумента style, чтобы получить четырехзначное значение года, включающее в себя век (гггг).

  Без века (гг) (1)	С веком (гггг)	Стандартная схема	Ввод/вывод (3)
  -	0 или 100 (1,2)	По умолчанию	мес дд гггг чч:мм AM (или PM)
  1	101	США	мм/дд/гггг
  2	102	ANSI	гг.мм.дд
  3	103	Британская/французская	дд/мм/гггг
  4	104	Немецкая	дд.мм.гг
  5	105	Итальянская	дд-мм-гг
  6	106(1)	-	дд мес гг
  7	107(1)	-	Мес дд, гг
  8	108	-	чч:мм:сс
  -	9 или 109 (1,2)	По умолчанию + миллисекунды	мес дд гггг чч:мм:сс:ммм AM (или PM)
  10	110	США	мм-дд-гг
  11	111	Японская	гг/мм/дд
  12	112	ISO	ггммдд
  -	13 или 113 (1,2)	Европейская по умолчанию + миллисекунды	дд мес гггг чч:мм:сс:ммм (24-часовой формат)
  14	114	-	чч:мм:сс:ммм (24-часовой формат)
  -	20 или 120 (2)	Каноническая для ODBC	гггг-мм-дд чч:мм:сс (24-часовой формат)
  -	21 или 121 (2)	Каноническая для ODBC (с миллисекундами)	гггг-мм-дд чч:мм:сс.ммм (24-часовой формат)
  -	126 (4)	ISO8601	гггг-мм-ддTчч:мм:сс.ммм (без пробелов)
  	127(6, 7)	ISO8601 с часовым поясом П.	гггг-мм-ддTчч:мм:сс.мммП (без пробелов)
  -	130 (1,2)	Хиджра (5)	дд мес гггг чч:мм:сс:ммм AM (или PM)
  -	131 (2)	Хиджра (5)	дд/мм/гг чч:мм:сс:ммм AM (или PM)

  1 Эти значения стилей возвращают неопределенные результаты. Включают в себя все стили «гг» (без номера века) и часть стилей «гггг» (с номером века).

  2 Значения по умолчанию (style0 или 100, 9 или 109, 13 или 113, 20 или 120 и 21 или 121) всегда возвращают год с веком (гггг).

  3 Вход при преобразовании в тип datetime; выход при преобразовании в символьные данные.

  4 Для использования в формате XML. Для преобразования из datetime или smalldatetime в символьные данные формат вывода должен быть таким как это описано в предыдущей таблице.

  5 Хиджра — календарная система с несколькими вариантами. В SQL Server 2005 используется кувейтский алгоритм.

  Важно!

  По умолчанию SQL Server интерпретирует двузначные значения года с пороговым значением 2049. То есть год 49 интерпретируется как 2049, а год 50 — как 1950. Многие клиентские приложения, в частности, основывающиеся на объектах OLE-автоматизации, используют для этого значение 2030. В SQL Server есть параметр конфигурации two digit year cutoff, меняющий пороговое значение года SQL Server, что позволяет согласовывать работу с датами. Рекомендуется использовать четырехзначные года.

  6 Поддерживается только при приведении символьных данных к типу datetime или smalldatetime. При приведении символьных данных, представляющих только дату или только время, к типам datetime и smalldatetime неуказанное время устанавливается в 00:00:00.000, а неуказанная дата — в 1900-01-01.

  7 Необязательный признак часового пояса П используется для упрощения сопоставления XML-значений типа datetime, содержащих сведения о часовом поясе, со значениями SQL Server типа datetime без таких сведений. П — это признак часового пояса UTC-0. Другие пояса обозначаются смещением в формате ЧЧ:ММ в направлении + или -. Например: 2006-12-12T23:45:12-08:00.

  При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.

  В следующей таблице показаны значения аргумента style, использующиеся для преобразования значений типа float и real в символьные данные.

  Значение	Выход
  0 (по умолчанию)	Не более 6 разрядов. По необходимости используется экспоненциальное представление чисел.
  1	Всегда 8 разрядов. Всегда используется экспоненциальное представление чисел.
  2	Всегда 16 разрядов. Всегда используется экспоненциальное представление чисел.

  Примечание.
  Если для преобразования из типов float и real указан стиль 126, вывод эквивалентен значениям style, равным соответственно 2 и 1.

  В следующей таблице показаны значения параметра style, использующиеся для преобразования значений типа money и smallmoney в символьные данные.

  Значение	Выход
  0 (по умолчанию)	Без запятых, разделяющих группы разрядов, с двумя цифрами справа от десятичного разделителя, например 4235,98.
  1	Запятые, разделяющие группы из трех разрядов слева от десятичной точки, с двумя цифрами справа от десятичного разделителя, например 3,510.92.
  2	Без запятых, разделяющих группы разрядов, с четырьмя цифрами справа от десятичного разделителя, например 4235,9819.

  Примечание.
  Если для преобразования из типа money или smallmoney в символьные данные указан стиль 126, вывод эквивалентен значению style, равному 2.

  В следующей таблице показаны значения параметра style, используемые для преобразования строкового ввода в данные типа xml.

  Значение

Типы возвращаемых данных

Возвращает значение такого же типа, что и аргумент data_type.

Замечания

Неявные преобразования происходят, когда функции CAST или CONVERT не вызываются. Явные преобразования требуют вызова функции CAST или CONVERT. На следующей иллюстрации показаны все явные и неявные преобразования типов данных, допустимые для системных типов данных SQL Server 2005. Сюда входят типы xml, bigint и sql_variant. При присваивании неявного преобразования от типа sql_variant не происходит, но неявное преобразование к типу sql_variant производится.

Примечание.
Так как у данных в Юникоде всегда четное число байтов, будьте осторожны при преобразовании значений типа binary или varbinary к типам данных, поддерживающим Юникод, и наоборот. Например, следующее преобразование не вернет шестнадцатеричное значение 41, оно вернет 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Типы данных большого объема

Типы данных большого объема демонстрируют то же поведение при явных и неявных преобразованиях, что и их аналоги меньшего объема, а именно типы данных varchar, nvarchar и varbinary. Однако необходимо учитывать следующие правила:

• преобразование из image в varbinary(max) и обратно неявное, как и преобразования между text и varchar(max) и ntext и nvarchar(max);
• преобразование из типов данных большого объема, например varchar(max), в аналогичный тип данных меньшего объема, например varchar, неявное, но если объем данных слишком велик, будет произведено усечение данных до указанной длины конкретного типа данных меньшего объема;
• преобразование из varchar, nvarchar или varbinary в соответствующие им типы данных большого объема выполняется неявно;
• преобразование из типа данных sql_variant в типы данных большого объема выполняется явно.
• типы данных большого объема не могут быть преобразованы в тип данных sql_variant.

Сведения о преобразовании пользовательских типов среды Microsoft .NET Framework CLR см. в разделе Выполнение операций над пользовательскими типами данных. Дополнительные сведения о преобразовании из типа данных xml см. в разделе Формирование XML-экземпляров.

Тип данных xml

При явном или неявном приведении типа данных xml к строковому или двоичному типу данных, содержимое типа данных xml сериализуется согласно набору определенных правил. Сведения об этих правилах см. в разделе Сериализация XML-данных. Сведения о приведении типа данных XML к пользовательскому типу данных CLR см. в разделе Выполнение операций над пользовательскими типами данных. Сведения о преобразовании из других типов данных к типу данных xml см. в разделе Формирование XML-экземпляров.

Типы данных text и image

Для типов данных text и image автоматическое преобразование типов не поддерживается. Можно явно преобразовать text в символьные данные, а image — в binary или varbinary, но длиной не более 8 000 байт. Если вы попробуете произвести неправильное преобразование, например преобразовать символьное выражение, содержащее буквы, в int, SQL Server вернет ошибку.

Параметры сортировки выходных данных

Если входные и выходные данные функций CAST и CONVERT — символьные строки, у выходных данных будут те же параметры сортировки, что и у входных. Если входные данные не символьная строка, выходным назначаются параметры сортировки по умолчанию для этой базы данных и присваивается метка о том, что этот режим используется принудительно. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).

Чтобы назначить выходным данным другие параметры сортировки, примените предложение COLLATE к результирующему выражению функции CAST или CONVERT. Например:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Усечение и округление результатов

При преобразовании символьных или двоичных выражений (char, nchar, nvarchar, varchar, binary или varbinary) к выражению другого типа данных данные могут быть усечены, отображаться только частично или вызывать ошибку, так как результат слишком мал для отображения. Результаты преобразований в char, varchar, nchar, nvarchar, binary и varbinary усекаются всегда, за исключением случаев, перечисленных в таблице ниже.

* = результат слишком мал для отображения. О = ошибка, так как длина результата слишком мала для отображения.

SQL Server гарантирует получение одинаковых результатов в разных версиях только для обратимых преобразований, то есть таких, когда данные преобразуются из исходного типа данных, а затем опять в него. В следующем примере показано обратимое преобразование:

DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Примечание.
Не пытайтесь составлять данные типа binary, а затем преобразовывать их к данным численного типа. SQL Server не гарантирует, что результат преобразования типа данных decimal или numeric в binary будет одинаковым в разных версиях SQL Server.

В следующем примере показано результирующее выражение, которое слишком мало для отображения:

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

Ниже приводится результирующий набор.

FirstName      LastName         Title                  Sick Leave
---------      ---------      -------------------   -----------
Gustavo        Achong         Mr.                   *
Catherine      Abel           Ms.                   *
Kim            Abercrombie    Ms.                   *
Humberto       Acevedo        Sr.                   *
Pilar          Ackerman       Sra.                  *

(5 row(s) affected)

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

Например, результатом следующего преобразования будет 10:

SELECT CAST(10.6496 AS int)

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

SELECT CAST(10.3496847 AS money)

SQL Server возвращает сообщение об ошибке при попытке преобразовать нечисловые данные типа char, nchar, varchar или nvarchar к типам int, float, numeric и decimal. SQL Server также возвращает ошибку, если к типам numeric и decimal преобразуется пустая строка (« »).

Преобразование двоичных строковых данных

Если при преобразовании данных типа binary или varbinary в символьные после х указано нечетное число значений, SQL Server добавит ноль (0) после х для получения четного числа значений.

Двоичные данные составлены из двухсимвольных групп, где символы принимают значения от 0 до 9 и от A до F (или от a до f). Двоичные строки должны начинаться с 0x. Например, чтобы ввести FF, введите 0xFF. Максимальным значением будет двоичное значение длиной 8 000 байт, каждый из них представляет собой FF. Типы данных binary предназначены не для шестнадцатеричных данных, а для битовых шаблонов. Преобразования и вычисления с использованием шестнадцатеричных чисел, хранящихся в виде двоичных данных, ненадежны.

При указании длины типа данных binary каждые два символа считаются за один. Длина 10 означает, что будут введены 10 двухсимвольных групп.

Пустые двоичные строки представляются в виде 0x и могут храниться в виде двоичных данных.

Примеры

А. Использование функций CAST и CONVERT

В каждом примере получаются имена продуктов, у которых первая цифра цены — 3, для этого их значения ListPrice преобразовываются к int.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

Б. Использование функции CAST с арифметическими операторами

В следующем примере вычисляется столбец значений (Computed) путем деления суммарных продаж за год (SalesYTD) на проценты комиссионных (CommissionPCT). Результат преобразуется к типу данных int после округления до ближайшего целого числа.

USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Ниже приводится результирующий набор.

Computed      
------ 
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)

В. Использование функции CAST для сцепления строк

В следующем примере несимвольные недвоичные выражения сцепляются с помощью функции CAST.

USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

Ниже приводится результирующий набор.

ListPrice
------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09

(5 row(s) affected)

Г. Использование функции CAST для получения удобочитаемого текста

В следующем примере функция CAST используется для преобразования значений столбца Name к значениям типа char(10).

USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Ниже приводится результирующий набор.

Name       UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99

(3 row(s) affected)

Д. Использование функции CAST с предложением LIKE

В следующем примере столбец значений money типа SalesYTD преобразуется в тип int, а затем в тип char(20) так, чтобы его можно было использовать в предложении LIKE.

USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

Ниже приводится результирующий набор.

FirstName        LastName            SalesYTD         SalesPersonID
---------------- ------------------- ---------------- -------------
Carol            Elliott             2811012.7151      279
Julie            Estes               219088.8836       288
Janeth           Esteves             2241204.0424      289

(3 row(s) affected)

Е. Использование функции CONVERT или CAST с типизированным XML

В следующих нескольких примерах показано использование функции CONVERT для преобразования данных в типизированный XML-код при помощи типа данных xml.

В этом примере строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором удаляются все незначащие пробельные символы (пробелы, разделяющие узлы):

CONVERT(XML, '<root><child/></root>')

В этом примере похожая строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором сохраняются все незначащие пробельные символы (пробелы, разделяющие узлы):

CONVERT(XML, '<root>          <child/>         </root>', 1)

В этом примере строка, содержащая пробельные символы, текст и разметку, приводится к типизированному XML:

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Дополнительные примеры см. в разделе Формирование XML-экземпляров.

Ж. Использование функций CAST и CONVERT с типом данных datetime

Следующий пример показывает текущую дату и время, использует функцию CAST для изменения текущей даты и времени в символьный тип данных и затем использует CONVERT для отображения даты и времени в формате ISO 8901.

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

Ниже приводится результирующий набор.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

Следующий пример — частичная противоположность предыдущему примеру. Пример отображает дату и время в виде символьных данных, использует функцию CAST для изменения символьных данных в тип данных datetime, а затем использует CONVERT для изменения символьных данных в тип данных datetime.

SELECT 
   '2006-04-04T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Ниже приводится результирующий набор.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997

(1 row(s) affected)

См. также

Справочник

SELECT (Transact-SQL)
Системные функции (Transact-SQL)

Другие ресурсы

Преобразование типов данных (компонент Database Engine)
Формат ISO 8601
Написание интернационализированных инструкций на языке Transact-SQL

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений