Prima di cominciare, voglio tranquillizzare tutti: lo so benissimo che non si dovrebbe mai confondere il livello dei dati (di cui è responsabile il database) con il livello della presentazione dei dati (di cui è responsabile l’interfaccia utente). Ma qualche volte mi è capitato di voler semplicemente rendere più leggibile l’output di una query estemporanea, senza creare un applicazione ad hoc o anche solo agganciarla a Reporting Services.
Presento quindi una piccola serie di user function, che ricevono un valore in input e lo restituiscono formattato; ribadisco ancora che lo scopo non è quello di sostituirsi ad una vera interfaccia utente, quindi, anche se ho cercato di renderle minimamente configurabili, scordatevi subito gestione della localizzazione, traduzioni e compagnia bella. Queste funzioni sono per uso personale, se le prendete, adattatevele pure sulle vostre necessità o preferenze.
Un’altra precisazione; per un po’ di tempo, negli script di creazione di oggetti ho adottato la stessa strategia di Microsoft: se l’oggetto esiste, cancellalo, poi – in ogni caso crealo (IF EXISTS(...) DROP...;GO CREATE...
). Da un po’ di tempo, preferisco invece l’approccio inverso: se l’oggetto non esiste, crealo – in forma minima – quindi modificalo nella sua versione definitiva (IF NOT EXISTS(...) EXECUTE('CREATE ...');GO ALTER...
). A parte il fatto che in questo modo non è necessario ri-attribuire eventuali permission all’oggetto, c’é da notare che l’istruzione CREATE
deve essere lanciata attraverso una EXECUTE
in quanto pretende di essere la prima istruzione del batch ad essere eseguita.
Premesso tutto ciò, passiamo decisamente all’azione.
Numeri interi
La prima funzione che presento, restituisce un numero BigInt
in una stringa formattata di lunghezza desiderata e – eventualmente – usando il separatore delle migliaia desiderato.
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE [Object_Id] = Object_Id( '[dbo].[FormatBigInt]' ) ) EXECUTE ( 'CREATE FUNCTION [dbo].[FormatBigInt]() RETURNS INT AS BEGIN RETURN 1; END' ); GO ALTER FUNCTION [dbo].[FormatBigInt] ( @num bigint , @length int , @separator char(1) , @paddingChar char(1) ) RETURNS varchar(max) AS BEGIN DECLARE @out nvarchar(max); DECLARE @padded nvarchar(max); DECLARE @index int; IF ( @separator = '' ) OR ( @separator IS NULL ) SET @separator = '.'; IF ( @paddingChar = '' ) OR ( @paddingChar IS NULL ) SET @paddingChar = ' '; SET @padded = Replicate( @paddingChar, @length); IF ( @paddingChar != ' ' ) BEGIN SET @index = @length - 3; WHILE @index > 0 BEGIN SET @padded = STUFF ( @padded, @index, 1, @separator); SET @index = @index - 4; END END IF ( @num = 0 ) BEGIN SET @out = @padded END ELSE BEGIN SET @out = ''; WHILE @num > 0 BEGIN SET @out = Right( '000' + convert ( varchar(3), @num % 1000), 3 ) + Coalesce( @separator + @out, '' ); SET @num = convert( bigint, @num / 1000 ); END SET @out = RTrim( @out ); SET @out = LTrim( @out ); SET @out = Left ( @out, Len( @out) - 1 ); WHILE ( Left( @out, 1 ) = '0' ) BEGIN SET @out = Right( @out, Len( @out ) - 1 ); END SET @out = STUFF( @padded, @length - Len(@out) + 1, Len(@out), @out ); SET @out = Right( @out, @length ); END RETURN @out END GO
Sinceramente non mi sembra ci sia molto di particolare da dire. Possiamo anche metterla alla prova con alcuni test:
SELECT [dbo].[FormatBigInt]( 1, 2, NULL, NULL); SELECT [dbo].[FormatBigInt]( 1, 2, NULL, '0'); SELECT [dbo].[FormatBigInt]( 1, 2, '''', NULL); SELECT [dbo].[FormatBigInt]( 1, 2, '''', '0'); SELECT [dbo].[FormatBigInt]( 0, 25, NULL, NULL); SELECT [dbo].[FormatBigInt]( 0, 25, NULL, '0'); SELECT [dbo].[FormatBigInt]( 0, 25, '''', NULL); SELECT [dbo].[FormatBigInt]( 0, 25, '''', '0'); SELECT [dbo].[FormatBigInt]( 987654321, 25, NULL, NULL); SELECT [dbo].[FormatBigInt]( 987654321, 25, NULL, '0'); SELECT [dbo].[FormatBigInt]( 987654321, 25, '''', NULL); SELECT [dbo].[FormatBigInt]( 987654321, 25, '''', '0');
ed otterremo cone risultato:
------------------------- 1 (1 row(s) affected) ------------------------ 01 (1 row(s) affected) ------------------------- 1 (1 row(s) affected) ------------------------- 01 (1 row(s) affected) ------------------------- (1 row(s) affected) ------------------------- 0.000.000.000.000.000.000 (1 row(s) affected) ------------------------- (1 row(s) affected) ------------------------- 0'000'000'000'000'000'000 (1 row(s) affected) ------------------------- 987.654.321 (1 row(s) affected) ------------------------- 0.000.000.000.987.654.321 (1 row(s) affected) ------------------------- 987'654'321 (1 row(s) affected) ------------------------- 0'000'000'000'987'654'321 (1 row(s) affected)
Formattazione di valori decimali
Tanto per cominciare, questa funzione userà la precedente per formattare la parte intera del numero decimale; quindi anche i parametri da passare saranno (almeno) gli stessi. Bando alle ciance, passiamo tosto al codice:
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE [Object_Id] = Object_Id( '[dbo].[FormatMoney]' ) ) EXECUTE ( 'CREATE FUNCTION [dbo].[FormatMoney]() RETURNS INT AS BEGIN RETURN 1; END' ); GO ALTER FUNCTION [dbo].[FormatMoney] ( @input money , @length int , @paddingChar char(1) = NULL , @thousandSeparator char(1) , @decimalSeparator char(1) ) RETURNS varchar(max) AS BEGIN IF ( @paddingChar = '' ) OR ( @paddingChar IS NULL ) SET @paddingChar = ' '; IF ( @thousandSeparator = '' ) OR ( @thousandSeparator IS NULL ) SET @thousandSeparator = '.'; IF (@decimalSeparator IS NULL) OR (@decimalSeparator = '') SET @decimalSeparator = ','; DECLARE @result varchar( max ); DECLARE @decimalPosition int; DECLARE @integerPart bigint; DECLARE @decimalPart varchar(max); SET @result = CONVERT( varchar(max), @input, 2); SET @decimalPosition = CharIndex( '.', @result ); SET @integerPart = Convert( bigint, SubString( @result, 1, @decimalPosition - 1 ) ); SET @decimalPart = SubString( @result, @decimalPosition + 1, Len( @result ) - @decimalPosition ); SET @result = dbo.FormatBigInt( @integerPart, @length - Len( @decimalPart ) - 1, @paddingChar, @thousandSeparator ) + @decimalSeparator + @decimalPart; RETURN @result; END GO
Vogliamo metterla alla prova?
SELECT [dbo].[FormatMoney]( 987654321, 27, NULL, NULL, NULL); SELECT [dbo].[FormatMoney]( 987654321, 27, NULL, NULL, '.'); SELECT [dbo].[FormatMoney]( 987654321, 27, NULL, '''', NULL); SELECT [dbo].[FormatMoney]( 987654321, 27, NULL, '''', '.'); SELECT [dbo].[FormatMoney]( 987654321, 27, '0', NULL, NULL); SELECT [dbo].[FormatMoney]( 987654321, 27, '0', NULL, '.'); SELECT [dbo].[FormatMoney]( 987654321, 27, '0', '''', NULL); SELECT [dbo].[FormatMoney]( 987654321, 27, '0', '''', '.'); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, NULL, NULL, NULL); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, NULL, NULL, '.'); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, NULL, '''', NULL); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, NULL, '''', '.'); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, '0', NULL, NULL); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, '0', NULL, '.'); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, '0', '''', NULL); SELECT [dbo].[FormatMoney]( 9888777666555.44, 27, '0', '''', '.');
Ed otteniamo:
--------------------------- 987.654.321,0000 (1 row(s) affected) --------------------------- 987.654.321.0000 (1 row(s) affected) --------------------------- 987'654'321,0000 (1 row(s) affected) --------------------------- 987'654'321.0000 (1 row(s) affected) --------------------------- 00.000.000.987.654.321,0000 (1 row(s) affected) --------------------------- 00.000.000.987.654.321.0000 (1 row(s) affected) --------------------------- 00'000'000'987'654'321,0000 (1 row(s) affected) --------------------------- 00'000'000'987'654'321.0000 (1 row(s) affected) --------------------------- 9.888.777.666.555,4400 (1 row(s) affected) --------------------------- 9.888.777.666.555.4400 (1 row(s) affected) --------------------------- 9'888'777'666'555,4400 (1 row(s) affected) --------------------------- 9'888'777'666'555.4400 (1 row(s) affected) --------------------------- 00.009.888.777.666.555,4400 (1 row(s) affected) --------------------------- 00.009.888.777.666.555.4400 (1 row(s) affected) --------------------------- 00'009'888'777'666'555,4400 (1 row(s) affected) --------------------------- 00'009'888'777'666'555.4400 (1 row(s) affected)
Formattazione di valori time
Spero di non cogliere nessuno di sorpresa, ma questa funzione… formatta dei valori interi, i secondi, in una stringa nel formato "hh:mm:ss"
; per intenderci la stringa così ottenuta può essere passata alla WAITFOR DELAY
(se non si fosse capito, è stata la necessità da cui è nata la funzione).
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE [Object_Id] = Object_Id( 'dbo.FormatTime' ) ) EXECUTE ( 'CREATE FUNCTION dbo.FormatTime() RETURNS int AS BEGIN RETURN 1; END'); GO ALTER FUNCTION dbo.FormatTime ( @seconds int , @separator varchar(1) ) RETURNS char(8) AS BEGIN IF @separator IS NULL SET @separator = ':'; DECLARE @result char(8); DECLARE @hours int; DECLARE @minutes int; SET @hours = Convert ( int, @seconds / 3600 ); SET @minutes = Convert ( int, ( @seconds - ( 3600 * @hours ) ) / 60 ); SET @seconds = @seconds - ( 3600 * @hours ) - ( 60 * @minutes ); SET @result = dbo.FormatBigInt( @hours, 2, '0', '' ) + @separator + dbo.FormatBigInt( @minutes, 2, '0', '' ) + @separator + dbo.FormatBigInt( @seconds, 2, '0', '' ); RETURN @result END GO
Metterla alla prova è molto semplice:
SELECT dbo.FormatTime( 187, NULL ); SELECT dbo.FormatTime( 95, '' ); SELECT dbo.FormatTime( 3661, '.' );
Risultati? Pronti:
-------- 00:03:07 (1 row(s) affected) -------- 000135 (1 row(s) affected) -------- 01.01.01 (1 row(s) affected)