W SQL Server można użyć funkcji T-SQL FORMAT() aby zwrócić wartości takie jak liczby i daty jako sformatowane ciągi znaków.
Podajemy wartość do sformatowania i określamy format, który ma być użyty. Funkcja przyjmuje opcjonalny argument, który pozwala na określenie kultury, która ma być użyta podczas formatowania wartości.
Syntaktyka
Składnia wygląda następująco:
FORMAT ( value, format )
Gdzie value jest wartością, którą chcesz sformatować, a format jest prawidłowym łańcuchem formatu, który określa pożądany format.
Opcjonalny argument culture może być użyty do określenia kultury. Jeśli zostanie pominięty, używany jest język bieżącej sesji.
Funkcja FORMAT jest niedeterministyczna.
Przykład 1 – Formatuj liczbę
Tutaj przykład formatowania liczby:
SELECT FORMAT(1, 'N') AS Result;
Wynik:
+----------+| Result ||----------|| 1.00 |+----------+
W tym przypadku jako ciąg formatu użyłem N. Jest to standardowy numeryczny specyfikator formatu do wyprowadzania wartości jako liczby. Ten konkretny specyfikator formatu powoduje, że wartość wyjściowa jest sformatowana z cyframi całkowitymi i dziesiętnymi, separatorami grup i separatorem dziesiętnym z opcjonalnym znakiem ujemnym. Ten specyfikator formatu nie uwzględnia wielkości liter, więc albo N albo n jest w porządku.
Przykład 2 – Formatuj do waluty
Oto przykład formatowania liczby jako waluty:
SELECT FORMAT(1, 'C') AS Result;
Wynik:
+----------+| Result ||----------|| $1.00 |+----------+
Więcej formatów liczbowych
Istnieje o wiele więcej ciągów formatu, które mogą być używane z funkcją FORMAT(). Na przykład, istnieją łańcuchy formatu dla wartości procentowych, stałoprzecinkowych, wykładniczych (naukowych), szesnastkowych i innych.
Liczbowe łańcuchy formatu są podzielone na standardowe i niestandardowe.
Dwa poniższe artykuły zawierają listę wszystkich numerycznych łańcuchów formatowania wraz z przykładami:
- Standardowe numeryczne łańcuchy formatowania
- Niestandardowe numeryczne łańcuchy formatowania
Zobacz także Jak formatować liczby w SQL Server, aby poznać więcej przykładów formatowania liczb.
Przykład 3 – Formatowanie daty
Tutaj znajduje się przykład formatowania daty:
SELECT GETDATE() AS 'Unformatted Date', FORMAT( GETDATE(), 'D') AS 'Formatted Date';
Wynik:
+-------------------------+------------------------+| Unformatted Date | Formatted Date ||-------------------------+------------------------|| 2019-05-08 06:16:55.613 | Wednesday, May 8, 2019 |+-------------------------+------------------------+
W tym przypadku użyłem D, który określa długi wzorzec daty.
Więcej formatów daty
Istnieje wiele innych ciągów formatu, które mogą być użyte do formatowania dat. Podobnie jak w przypadku numerycznych łańcuchów formatu, łańcuchy formatu daty i czasu są podzielone na standardowe i niestandardowe, więc możesz tworzyć własne formaty lub polegać na standardowych.
Dwa poniższe artykuły zawierają listę wszystkich łańcuchów formatu daty i czasu wraz z przykładami:
- Standardowe łańcuchy formatu daty i czasu
- Niestandardowe łańcuchy formatu daty i czasu
Zobacz również Jak sformatować datę & Czas w SQL Server, aby poznać więcej przykładów.
Przykład 4 – Opcjonalny argument kultury
Tutaj znajduje się przykład użycia argumentu culture do zwrócenia wartości w różnych walutach:
SELECT FORMAT(1, 'C', 'fr-FR') AS 'France', FORMAT(1, 'C', 'th-TH') AS 'Thailand', FORMAT(1, 'C', 'ja-JP') AS 'Japan';
Wynik:
+----------+------------+---------+| France | Thailand | Japan ||----------+------------+---------|| 1,00 € | ฿1.00 | ¥1 |+----------+------------+---------+
Funkcja FORMAT() przyjmuje jako argument dowolną kulturę obsługiwaną przez .NET Framework jako argument; nie jest ograniczona do języków jawnie obsługiwanych przez SQL Server.
Jeśli nie podasz tego argumentu, używany jest język bieżącej sesji.
Po więcej przykładów zobacz How Language Settings can Affect your FORMAT() Results.
Przykład 5 – Invalid culture Argument
Jeśli podasz nieprawidłową kulturę, otrzymasz błąd:
SELECT FORMAT(1, 'C', 'oop-SS!') AS 'Oops!';
Wynik:
The culture parameter 'oop-SS!' provided in the function call is not supported.
Przykład 6 – Invalid Format Value
Jednakże w przypadku innych błędów funkcja zwraca NULL. Na przykład, oto co się stanie, jeśli podam nieprawidłową wartość do sformatowania:
SELECT FORMAT(GETDATE(), 'C') AS 'Result';
Wynik:
+----------+| Result ||----------|| NULL |+----------+
W tym przypadku próbowałem sformatować datę na walutę, dlatego wynikiem było NULL.
Dopuszczalne typy danych
FORMAT() opiera się na obecności .NET Framework Common Language Runtime (CLR) i używa reguł formatowania CLR.
Następujące typy danych mogą być formatowane za pomocą funkcji FORMAT(). Lista ta zawiera dopuszczalne typy danych dla łańcucha wejściowego wraz z ich odpowiednikami odwzorowującymi typy .NET Framework.
| Kategoria | Typ | .NET type |
|---|---|---|
| Numeric | bigint | Int64 |
| Numeric | int | Int32 |
| Numeric | smallint | Int16 |
| Numeric | tinyint | Byte |
| Numeric | decimal | SqlDecimal |
| Numeric | numeric | SqlDecimal |
| Numeric | float | Double |
| Numeric | real | Single |
| Numeric | smallmoney | Decimal |
| Numeric | money | Decimal |
| Date and Time | date | DateTime |
| Date and Time | time | TimeSpan |
| Date and Time | datetime | DateTime |
| Date and Time | smalldatetime | DateTime |
| Data i czas | datetime2 | DateTime |
| Data i czas | datetimeoffset | DateTimeOffset |
Escaping Colons and Periods for the 'time' Data Type
Przy użyciu FORMAT, dwukropki i kropki muszą zostać usunięte (jest to zgodne z regułami formatowania CLR). Dlatego, gdy łańcuch formatu (drugi parametr) zawiera dwukropek lub kropkę, dwukropek lub kropka muszą być usunięte za pomocą odwrotnego ukośnika, gdy wartość wejściowa (pierwszy parametr) jest typu danych time.
Przykład:
SELECT CAST('12:15' AS time) AS 'Unformatted Data', FORMAT(CAST('12:15' AS time), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS time), N'hh\.mm') AS 'Escaped';
Wynik:
+--------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||--------------------+-------------+-----------|| 12:15:00 | NULL | 12.15 |+--------------------+-------------+-----------+
Więc zgodnie z oczekiwaniami, nie escape’owany ciąg zwraca NULL.
Jak wspomniano, dotyczy to tylko typu danych time. Jeśli zmienimy wartość wejściową na inny typ danych, nie musimy jej uciekać:
SELECT CAST('12:15' AS datetime) AS 'Unformatted Data', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Escaped';
Wynik:
+-------------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||-------------------------+-------------+-----------|| 1900-01-01 12:15:00.000 | 12.15 | 12.15 |+-------------------------+-------------+-----------+
W tym przypadku wartością wejściową jest datetime, a zatem wynik jest w porządku bez uciekania.
Możesz również użyć odwrotnego ukośnika do ucieczki dowolnego innego znaku, który chcesz zawrzeć w łańcuchu wynikowym, a który w przeciwnym razie zostałby zinterpretowany jako specyfikator formatu. Poprzedzenie znaku odwrotnym ukośnikiem oznacza, że następny znak jest literałem, który powinien być zawarty w łańcuchu wynikowym w niezmienionej postaci.
W niestandardowym łańcuchu formatu daty i czasu, dfFghHKmMstyz:, lub / znaki są interpretowane jako niestandardowe specyfikatory formatu, a nie jako znaki dosłowne.
W niestandardowym numerycznym ciągu formatu, znaki #0.,%, oraz ‰ symbole są interpretowane jako specyfikatory formatu, a nie jako dosłowne znaki. Wielkie i małe litery E, jak również symbole + i - mogą być również interpretowane jako specyfikatory formatu, w zależności od ich pozycji w łańcuchu formatu.
Jeśli musisz umieścić odwrotny ukośnik w łańcuchu wynikowym, ucieknij od niego za pomocą innego odwrotnego ukośnika.
Remotowanie
Funkcja FORMAT() nie może być remotowana, ponieważ zależy od obecności CLR. Remotowanie funkcji, która wymaga CLR, może spowodować błąd na zdalnym serwerze.
Kiedy używać funkcji FORMAT()
Microsoft zaleca, aby funkcja FORMAT() była używana do formatowania daty/czasu i wartości liczbowych jako ciągów, z uwzględnieniem ustawień lokalnych, a w przypadku ogólnych konwersji typów danych należy użyć funkcji CAST() lub CONVERT().