Data: 30.01.2023

SQL Server funkcje CAST i CONVERT oraz FORMAT

Spis treści

• SQL Server funkcje CAST i CONVERT oraz FORMAT
  • Rodzaje typów danych w MS SQL Server
  • Automatyczne rzutowanie typów danych w T-SQL
  • Funkcja CAST
  • Funkcja CONVERT
  • Funkcja FORMAT

W artykule znajdziesz informacje na temat typów danych w SQL oraz funkcji do konwersji typów danych – CAST i CONVERT używanych w języku Transact-SQL.

Przykłady możesz uruchomić na danych znajdujących się w bazach AdventureWorks i Northwind.

Środowisko SQL Server i bazy danych możesz zainstalować bezpłatnie. Na naszej stronie MS SQL Server instalacja znajdziesz opis, w jaki sposób zrobić instalację.

Rodzaje typów danych w MS SQL Server

W poszczególnych kolumnach w tabeli bazy danych SQL Server przechowujemy dane określonego typu np. liczby, teksty, daty i godziny.

Podstawowe typy danych w języku T-SQL:

1. Numeryczne – typy danych do przechowywania liczb w kolumnie tabeli. Wśród nich wyróżniamy:
   • int – typ do przechowywania liczb całkowitych
   • bigint – jest dwukrotnie większy od typu int i pozwala przechowywać większe wartości
   • decimal(precyzja, skala) – służy do przechowywania liczb z częścią ułamkową, stałopozycyjnych z określoną ilością miejsc po przecinku i przed przecinkiem np. używamy go do podawania kwot pieniężnych
   • float – służy głównie do zastosowań naukowych,  jesli chcemy przechowywać w bazie liczby o bardzo szerokim zakresie np. od blikich zeru do nieskończoności
2. Znakowe – służą do przechowywania ciągów znaków (tekstów). Wśród nich wyróżniamy:
   • varchar – reprezentuje ciąg znaków o zmiennej długości. Każdy wiersz kolumny ma zmienną długość wynikającą z liczby znaków zapisywanego łańcucha
   • nvarchar – reprezentuje ciąg znaków o zmiennej długości zakodowanych w UNICODE (bezpośrednia obsługa polskich znaków)
   • char – reprezentuje tekst o stałej długości. Cała kolumna w bazie danych będzie miała długość wskazaną parametrem „długość”. Jeśli dane będą miały mniej znaków, pozostałe miejsca zostaną uzupełnione spacjami.
   • nchar – reprezentuje tekst o stałej długości zakodowanych w UNICODE (bezpośrednia obsługa polskich znaków)
3. Daty i czasu – przechowują daty i czas. Wśród nich wyróżniamy:
   • DATETIME – reprezentuje datę i czas
   • DATETIME2 – typ przechowujący datę tak jak DATETIME ale ma większy zakres dat
   • DATE – służy do przechowywania daty
   • TIME – reprezentuje czas

Informacje na temat typów danych i odczytywania danych z baz znajdziesz również w naszym artykule MS SQL odczytywanie danych.

Automatyczne rzutowanie typów danych w T-SQL

SQL Server przeprowadza automatyczną konwersję (konwersja niejawna) typu tekstowego na liczbowy.

Przykład

W poniższym przykładzie w poleceniu drugim następuje niejawna konwersja tekstowo podanej dwójki i w wyniku otrzymujemy wartość 4.

Niejawna konwersja na typ int jest dokonywana, jeśli w wyrażeniu jest typ liczbowy i tekstowy oraz tekst może być automatycznie przekonwertowany na liczbę. Gdybyśmy mieli działanie 'a’+1 konwersja niejawna nie byłaby możliwa.

W trzecim zapytaniu konwersja nie jest dokonywana, gdyż wyrażenie zawiera dwa teksty. Obydwie dwójki są podane tekstowo i zostają złączone jako teksty.

SELECT 2+2;
SELECT '2'+2;
SELECT '2'+'2';

Wynik zapytania 1: 4

Wynik zapytania 2: 4

Wynik zapytania 3: 22

Funkcje konwersji CAST() i CONVERT() umożliwiają dokonywanie jawnej konwersji jednego typu na drugi.

Przykład

W przykładzie tekstowo podana dwójka została przekonwertowana za pomocą funkcji CAST() na liczbę całkowitą (int).

SELECT CAST('2' AS INT) + CAST('2' AS INT);

Wynik zapytania: 4

Funkcja CAST

CAST() to podstawowa funkcja do konwersji danych.

Składnia funkcji:

CAST(wartość, którą chcemy przekonwertować AS typ, na który konwertujemy).

Po słowie kluczowym AS podajemy typ danych, których chcemy otrzymać w wyniku konwersji.

Przykład

Poniższe zapytanie:

• wyświetla wartość z kolumny Freight
• konwertuje wartość z kolumny Freight z typu money na typ tekstowy (varchar).

SELECT Freight, CAST(Freight AS VARCHAR(10)) as Freight_tekst
FROM Orders;

Wynik zapytania:

Przykład

W przykładzie:

• wyświetlamy kolumny Name, SalesYTD
• konwertujemy za pomocą funkcji CAST() typ danych money na integer
• wyświetlamy tylko te rekordy, w przypadku których nazwa terytorium to „Australia”.

SELECT Name, SalesYTD, Cast(SalesYTD as INT)
FROM Sales.SalesTerritory
WHERE Name = 'Australia';

Przykład

W przykładzie obliczamy procentowy wzrost sprzedaży rok do roku i wyświeltlamy wynik ze znakiem procentu.

Jeśli chcemy łączyć wartości liczbowe w danej kolumnie z tekstem, musimy wykonać konwersję liczb na tekst.

Łączymy tekst (znak procentu) z danymi wyliczonymi wyrażeniem: ROUND((SalesYTD/SalesLastYear*100),2) AS VARCHAR (100).

SELECT SalesYTD, SalesLastYear,
CAST(ROUND((SalesYTD/SalesLastYear*100),2) AS VARCHAR (100)) + '%' AS wynik
FROM Sales.SalesTerritory;

Wynik zapytania:

Przykład

W przykładzie:

• wybieramy kolumnę Terytorium (Name)
• do danych w kolumnie Name dodajemy dwukropek i spację. Następnie konwertujemy dane w kolumnie Sprzedaż zeszłoroczna (SalesLastYear) na typ tekstowy i dodajemy je do danych z kolumny Name.

Use AdventureWorks2017;
SELECT Name + ': ' + CAST(SalesLastYear as varchar (100))
FROM Sales.SalesTerritory;

Wynik zapytania:

Funkcja CAST należy do standardu ANSI SQL. Dzięki temu powinna być implementowana we wszystkich dialektach języka SQL. Skrypt napisany w SQL Server, który używa funkcji CAST możemy przenieść na np. serwer Oracle.

Funkcja CONVERT

Funkcji CONVERT() również używamy do konwersji jednego typu danych na inny.

Składnia funkcji:

CONVERT(typ docelowy, wartość_konwertowana, format).

Trzeci argument funkcji (format) jest opcjonalny.

Funkcja CONVERT() różni się od funkcji CAST() tym, że można w niej określić styl typu danych. Jest to głównie przydatne przy konwersji na typy danych daty i czasu. Przykładowo poniżej konwertowane są daty na różne formaty:

SELECT OrderDate,
CONVERT(VARCHAR(20), OrderDate,1) as date2,
CONVERT(VARCHAR(20), OrderDate,101) as date3,
CONVERT(VARCHAR(20), OrderDate,4) as date4,
CONVERT(VARCHAR(20), OrderDate,104) as date5
FROM Orders;

Wynik zapytania:

Szczegółowe informacje na temat stylu typu danych znajdziesz na stronie Microsoft Microsoft funkcja CONVERT

Przykład

W przykładzie:

• wyświetlamy datę bieżącą za pomocą funkcji GETDATE() w formacie data i czas
• za pomocą funkcji SQL CONVERT() konwertujemy datę bieżącą na format daty dzień, miesiąc, rok rozdzielanych kropką

SELECT GETDATE() data_biezaca,
CONVERT(NVARCHAR(30),GETDATE(), 104) data_konwersja;

Przykład

W przykładzie:

• wyświetlamy kolumnę SalesYTD
• za pomocą funkcji CONVERT konwertujemy typ Money na Varchar. Do konwersji używamy stylu oznaczonego jedynką (1) przeznaczonego dla typu money./li>
• za pomocą funkcji REPLACE() zamieniamy przecinek na spację.

SELECT SalesYTD,
REPLACE(CONVERT(VARCHAR, SalesYTD, 1), ',', ' ')
FROM Sales.SalesTerritory;

Funkcja FORMAT

Funkcja FORMAT() służy do konwersji wartości różnych typów na odpowiednio sformatowany łańcuch znaków (tekst). Możemy w niej określić format, na który ma być przekształcona dana wartość.

Składnia:

FORMAT(wartość, format)

Przykład

W przykładzie:

• wyświetlamy wartości z kolumny DueDate
• konwertujemy dane przechowywane w kolumnie DueDate za pomocą funkcji CONVERT na format dd-MM-yyyy. W trzecim, opcjonalnym argumencie funkcji
• podajemy wartość 104, która określa rodzaj formatu danych
• konwertujemy dane z kolumny DueDate za pomocą funkcji FORMAT

SELECT DueDate,
CONVERT (varchar(10), DueDate, 104) as Date,
FORMAT(DueDate,'dd-MM-yyyy') as Date2
FROM Purchasing.PurchaseOrderDetail;

Wynik zapytania:

Jarek Olechno
Programista SQL, Cloud Developer

Udostępnij wpis: