SQL Server 2012 - Użycie funkcji konwersji  

Autor: Damian Widera

Opublikowano: 2012-11-14

Konwersja wyniku uzyskanego po wykonaniu zapytania bazodanowego stanowi jedną z podstawowych operacji, przeprowadzaną zarówno przez administratorów, jak i deweloperów bazodanowych. Dodatkowo, konwersja danych używana jest bardzo często dla projektowania filtra zapytania, użytego w frazie WHERE lub warunku grupowania.

Możliwość efektywnego konwertowania danych stanowi więc jedno z ważniejszych zadań i umiejętności osób zajmujących się SQL Server. Z drugiej strony, brak efektywnej możliwości pisania filtrów czy wyrażeń grupujących wpływa negatywnie na wydajność zapytań.

SQL Server oferował funkcje CAST oraz CONVERT do wersji 2012, które umożliwiały konwersje oraz rzutowanie typów danych. Dokładny ich opis znajduje się na stronie MSDN: https://msdn.microsoft.com/en-us/library/ms187928.aspx.

Funkcja CAST pozwalała na zmianę wyrażenia określonego typu danych na inną metodę rzutowania. Składnia tej funkcji jest następująca:

CAST ( expression AS data_type [ ( length ) ] )

Przykładowe wywołanie tej funkcji jest następujące:

SELECT CAST(10.6496 AS int); 

SELECT CAST(10.6496 AS varchar); 

SELECT CAST('10.6496' AS int); 


-----------
10

(1 row(s) affected)


------------------------------
10.6496

(1 row(s) affected)


-----------
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '10.6496' to data type int.

Jak widać, użycie funkcji CAST nie zawsze kończy się powodzeniem. Z tego właśnie powodu należy ją rozważnie używać, zwłaszcza w warunkach filtra, gdzie tylko możliwe powinna być otoczona składnią TRY-CATCH.

Funkcja CONVERT pozwala konwertować typ danych, zgodnie z podanym stylem. Najczęściej używamy tej funkcji do konwersji typu daty i czasu, zwłaszcza w SQL Server 2005 i starszych, kiedy nie były dostępne typy danych DATE oraz TIME.

Składnia funkcji CONVERT jest następująca:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Poniższy przykład pokazuje, jak operować funkcją CONVERT. Pokazano w nim, jak z funkcji GETDATE() uzyskać część związaną z datą i casem oraz sformatować ją zgodnie z odpowiednim stylem:

SELECT 
    CONVERT(char(10),GETDATE(),121) as _dzisiaj
    ,CONVERT(char(8),GETDATE(),114) as _teraz


_dzisiaj   _teraz
---------- --------
2012-09-03 10:20:22

(1 row(s) affected)

Funkcja CONVERT (również CAST) może być zagnieżdżana, czego przykładem jest poniższe wyrażenie:

DECLARE @myval decimal (5, 2);
SET @myval = 193.57;
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval));

---------------------------------------
193.57000

(1 row(s) affected)

Po omówieniu funkcji dostępnych w starszych wersjach SQL Server, pora spojrzeć na nowe funkcje konwersji:

• PARSE,
• TRY_PARSE,
• TRY_CAST,
• TRY_CONVERT.

Wszystkie wyżej wymienione funkcje napisane są w języku .NET. 

Funkcja PARSE zwraca wyrażenie określonego typu. Składania tej funkcji jest następująca:

PARSE ( string_value AS data_type [ USING culture ] )

Parametr string_value jest typem nvarchar(4000), i jeżeli nie uda się go skonwertować na wyrażenie określone przez parametr data_type, to funkcja zgłosi błąd.

Opcjonalnym parametrem jest culture, który określa ustawienia regionalne, względem których będzie formatowany wynik działania funkcji. Jeżeli parametr nie zostanie określony, to zostanie wykorzystana informacja o ustawieniach regionalnych, dostępna w obiekcie sesji połączenia do serwera.

Generalnie, zastosowanie tej funkcji sprowadza się do konwertowania łańcucha znaków na typ daty, czasu lub typ numeryczny. Należy jednak pamiętać, że parsowanie informacji wiąże się z pewnym narzutem obliczeniowym i może mieć wpływ na wydajność serwera baz danych.

Przykładowe użycie funkcji PARSE jest następujące:

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result
GO
SET LANGUAGE 'Polish'
SELECT PARSE('12/16/2010' AS datetime2) 
GO

Poniższe wywołanie funkcji PARSE zakończy się błędem:

SELECT PARSE('abc' as int)

Msg 9819, Level 16, State 1, Line 1
Błąd podczas konwertowania wartości ciągu „abc” na typ danych int przy użyciu kultury „”.

Funkcja TRY_PARSE używana jest w analogiczny sposób co funkcja PARSE. Wyjątek stanowi sytuacja, w której funkcja PARSE zwraca wyjątek, wówczas funkcja TRY_PARSE zwraca NULL. Składnia funkcji TRY_PARSE jest identyczna jak funkcji PARSE:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Można więc napisać zapytanie, dla którego funkcja PARSE zwróciła błąd:

SELECT TRY_PARSE('abc' as int)

-----------
NULL

(1 row(s) affected)

Stosując funkcję TRY_PARSE, można wykryć, czy dana konwersja zakończyła się poprawnie, czy wartością zwróconą było NULL. Spójrzmy na poniższy przykład:

SELECT
    CASE WHEN TRY_PARSE('Damian' AS decimal USING 'pl-PL') IS NULL
        THEN 'Prawda'
        ELSE 'Fałsz'
END

Po jego uruchomieniu otrzymujemy NULL, ponieważ nie sposób skonwertować łańcucha znaków „Damian” na typ decimal.

Funkcja TRY_CAST rozszerza działanie omawianej wcześniej funkcji CAST o obsługę wyjątków, w sytuacji kiedy rzutowanie na określony typ danych zakończy się wyjątkiem.

Składnia tej funkcji jest następująca:

TRY_CAST ( expression AS data_type [ ( length ) ] )

Poniższy przykład zaczerpnięty z MSDN pokazuje sposób użycia funkcji TRY_CAST:

SET DATEFORMAT dmy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Po jego uruchomieniu uzyskamy wartość NULL, ponieważ wartości 31 nie da się zrzutować na poprawną wartość miesiąca. Należy jednak pamiętać, że gdyby użyć funkcji CAST, to SQL Server zwróciłby wyjątek:

Result
---------------------------
Msg 241, Level 16, State 1, Line 2
Konwersja na datę i/lub godzinę z ciągu znaków nie powiodła się.

Funkcja TRY_CONVERT rozszerza działanie funkcji CONVERT w zakresie obsługi wyjątków, w sytuacji kiedy konwersja wyrażenia zakończyłaby się błędem.

Składnia tej funkcji jest następująca:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Poniższy przykład zaczerpnięty z MSDN pokazuje sposób użycia funkcji TRY_CONVERT:

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Po jego uruchomieniu uzyskamy wartość NULL, ponieważ wartości 31 nie da się zrzutować na poprawną wartość miesiąca. Należy jednak pamiętać, że gdyby użyć funkcji CONVERT, to SQL Server zwróciłby wyjątek:

Result
---------------------------
Msg 241, Level 16, State 1, Line 2
Konwersja na datę i/lub godzinę z ciągu znaków nie powiodła się.