.png "SQL Server 2012 - Nowe funkcje daty i czasu"){kind=icon}
.png "SQL Server 2012 - Wprowadzenie")
SQL Server 2012 - Nowe funkcje logiczne oraz operujące na łańcuchach znaków .png "Udostępnij na: Facebook")
Autor: Damian Widera
Opublikowano: 2013-01-09
W SQL Server 2012 dodano cztery użyteczne funkcje. Dwie z nich są funkcjami logicznymi:
- CHOOSE,
- IIF.
Dwie pozostałe przydatne funkcje pozwalają łatwiej operować na łańcuchach znaków:
- CONCAT,
- FORMAT.
Funkcja CHOOSE zwraca element z listy, który znajduje się na określonej pozycji:
CHOOSE ( index, val_1, val_2 [, val_n ] )
Indeks musi być wartością większą od zera, a jeśli zostanie podany w taki sposób, że przekracza liczbę elementów tablicy, to funkcja zwraca NULL. Poniżej znajduje się przykład użycia funkcji CHOOSE:
Select Choose (1,'DEMO', 'Przyklad') As [Demo]
Select Choose (0,'DEMO', 'Przyklad') As [Demo]
Select Choose (-1,'DEMO', 'Przyklad') As [Demo]
Select Choose (3,'DEMO', 'Przyklad') As [Demo]
Demo
--------
DEMO
(1 row(s) affected)
Demo
--------
NULL
(1 row(s) affected)
Demo
--------
NULL
(1 row(s) affected)
Demo
--------
NULL
(1 row(s) affected)
Funkcję CHOOSE można użyć również w nieco bardziej rozbudowany sposób, tak aby sterować pierwszym parametrem określającym indeks. Pozwala to na jej nieco większą automatyzację:
Declare @t table(Position int)
Insert into @t values(1),(2),(3),(4)
Declare @FirstPosition int = (Select Top 1 Position from @t)
Select Choose (@FirstPosition,'Apple','Banana') As [Choose Demo]
GO
Choose Demo
-----------
Apple
(1 row(s) affected)
Funkcja IIF zwraca jedną z dwóch wartości, w zależności, czy wyrażenie logiczne, podane jako parametr, przyjmuje wartość TRUE lub FALSE:
IIF ( boolean_expression, true_value, false_value )
Działanie tej funkcji jest bardzo podobne do działania funkcji CASE, w szczególności wartości NULL obsługiwane są w identyczny sposób. Podobnie wygląda zagnieżdżanie funkcji CASE oraz IIF – obydwie mogą być zagnieżdżone maksymalnie dziesięciokrotnie.
Działanie funkcji IIF jest następujące – jeśli wyrażenie logiczne (boolean_expression) będzie prawdziwe, to funkcja zwróci wartość parametru true_value.
Poniżej znajduje się przykład użycia funkcji IIF, dwukrotnie zagnieżdżonej:
Declare @Num1 As Int = 1
Declare @Num2 As Int = 1
Select Result = IIF(@Num1 > @Num2, 'Pierwszy numer jest wiekszy',
IIF(@Num2 > @Num1,'Drugi numer jest wiekszy','Numery sa rowne'))
Result
---------------------------
Numery sa rowne
(1 row(s) affected)
Funkcja CONCAT łączy dwa lub więcej łańcuchów znakowych. Jej składnia jest następująca:
CONCAT ( string_value1, string_value2 [, string_valueN ] )
Funkcja CONCAT wymaga co najmniej dwóch elementów, aby połączyć je w jeden wynikowy łańcuch typu string. Każdy z tych elementów konwertowany jest na łańcuch znaków.
Jeśli paramter jest typem napisanym w CLR, to łańcuch wynikowy będzie typu nvarchar(MAX). Jesli natomiast którykolwiek z parametrów jest typu varbinary(MAX) lub varchar(MAX), to wynikiem będzie varchar(MAX). Natomiast, w przypadku, gdy którykolwiek z parametrów jest typu nvarchar (<=4000), to wynikiem będzie typ danych nvarchar(<=4000).
W pozostałych przypadkach otrzymamy varchar(8000), chyba że parametrem będzie łańcuch znaków typu nvarchar, to wynikiem musi być również nvarchar.
W poprzednich wersjach SQL Server programiści składali łańcuchy znakowe, używając operacji „+”, ale musieli zabezpieczyć się przed możliwością wystąpowania wartości NULL w kolumnach. W związku z tym, w poniższym przykładzie pierwsze zapytanie zwróci NULL, a dopiero drugie, w którym obsłużono NULL, zwróci oczekiwany łańcuch znaków:
Select 'Sql' + CAST(11 as varchar(10)) + Null+ 'SQL Server'
Select 'Sql' + CAST(11 as varchar(10)) + COALESCE(Null,' ') + 'SQL Server'
------------------------
NULL
(1 row(s) affected)
---------------------------
Sql11 SQL Server
(1 row(s) affected)
Obecnie można skorzystać z funkcji CONCAT i zapisać to zapytanie w następujący sposób:
Select Concat('Sql',11, Null, 'SQL', 'Server', 2012)
------------------------------------
Sql11SQLServer2012
Funkcja FORMAT pozwala na formatowanie podanej wartości zgodnie z podanym schematem i ustawieniami regionalnymi. Składnia funkcji jest następująca:
FORMAT ( value, format [, culture ] )
Funkcja FORMAT zwróci NULL w przypadku powstania błędu innego niż „culture is not valid”. Funkcja FORMAT została napisana w .NET CLR i będzie działała pod warunkiem, że na serwerze została zainstalowana odpowiednia biblioteka .NET Framework.
Poniżej przedstawiono sposób użycia funkcji FORMAT dla formatowania łańcucha znakowego jako wartości monetarnej, która jest zależna od podanych ustawień regionalnych:
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('ru'),('no'),('pl')
Declare @currency int = 200
Select Culture, FORMAT(@currency,'c',Culture)
From @t
Culture
---------- ---------------------
en-US $200.00
ru 200,00р.
no kr 200,00
pl 200,00 zł
Na kolejnym przykładzie pokazano, w jaki sposób można formatować datę:
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('fr')
Declare @dt Date = '06/15/2011'
Select Culture
,FORMAT(@dt,'d',Culture)
,FORMAT(@dt,'yyyy/MM/dd',Culture)
From @t
Culture
---------- ------------
en-US 6/15/2011 2011/06/15
fr 15/06/2011
Na ostatnim przykładzie pokazano, jak można użyć, w sposób podobny do funkcji REPLICATE, funkcję format:
SELECT
FORMAT(ProductID, '0000000000') AS strproductid
, ProductNumber
FROM Production.Product;
strproductid ProductNumber
-----------------
0000000001 AR-5381
0000000002 BA-8327
0000000994 BB-7421
0000000995 BB-8107
(...)