Sql Server: come presentare valori numerici in forma di stringa

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)

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*