Как перевести string в int sql
Перейти к содержимому

Как перевести string в int sql

  • автор:

Как перевести 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

Функция CAST в T-SQL и MS SQL Server

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

Функция Convert в T-SQL и MS SQL Server

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

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

В реализациях языка Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL может быть выполнено неявное преобразование типов. Так, например, в Transact-SQL (T-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. SQL Server и Sybase ASE. Transact-SQL при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int. Подробно о явном и неявном преобразовании типов в 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» .)

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

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

Консоль

Выполнить

Преобразование типов данных (ядро СУБД)

Преобразование типов данных происходит в следующих случаях:

  • При перемещении, сравнении или объединении данных одного объекта с данными другого объекта эти данные могут преобразовываться из одного типа в другой.
  • При передаче в переменную программы данных из результирующего столбца 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 производится.

Data type conversion table

Хотя на приведенной выше диаграмме показаны все явные и неявные преобразования, которые допускаются в 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 байт Изображение

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

В реализациях языка 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 Самый дорогой

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *