afiseaza si ce e dupa virgula...doar ca numeric
CREATE FUNCTION OriceNume( @nr varchar(50) )
RETURNS VARCHAR(255)
AS
BEGIN
declare @Sute table
(
Cifra int,
Text VARCHAR(25),
Biti SMALLINT
)
INSERT INTO @Sute (Cifra,Text) VALUES (0,'')
INSERT INTO @Sute (Cifra,Text) VALUES (1,'UnaSuta')
INSERT INTO @Sute (Cifra,Text) VALUES (2,'DouaSute')
INSERT INTO @Sute (Cifra,Text) VALUES (3,'TreiSute')
INSERT INTO @Sute (Cifra,Text) VALUES (4,'PatruSute')
INSERT INTO @Sute (Cifra,Text) VALUES (5,'CinciSute')
INSERT INTO @Sute (Cifra,Text) VALUES (6,'SaseSute')
INSERT INTO @Sute (Cifra,Text) VALUES (7,'SapteSute')
INSERT INTO @Sute (Cifra,Text) VALUES (8,'OptSute')
INSERT INTO @Sute (Cifra,Text) VALUES (9,'NouaSute')
UPDATE @Sute SET Biti = POWER(2,Cifra)
declare @Zeci table
(
Cifra int,
Text VARCHAR(25),
Biti SMALLINT
)
INSERT INTO @Zeci (Cifra,Text) VALUES (0,'')
INSERT INTO @Zeci (Cifra,Text) VALUES (1,NULL)
INSERT INTO @Zeci (Cifra,Text) VALUES (2,'DouaZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (3,'TreiZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (4,'PatruZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (5,'CinciZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (6,'Saizeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (7,'SapteZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (8,'OptZeci')
INSERT INTO @Zeci (Cifra,Text) VALUES (9,'NouaZeci')
UPDATE @Zeci SET Biti = POWER(2,Cifra)
declare @Unitati table
(
Cifra int PRIMARY KEY,
Text VARCHAR(25),
Biti SMALLINT
)
INSERT INTO @Unitati (Cifra,Text) VALUES (0,'') --daca nivelul este 0 atunci Text='Zero' altfel Text=''
INSERT INTO @Unitati (Cifra,Text) VALUES (1,'Unu')
INSERT INTO @Unitati (Cifra,Text) VALUES (2,'Doi')
INSERT INTO @Unitati (Cifra,Text) VALUES (3,'Trei')
INSERT INTO @Unitati (Cifra,Text) VALUES (4,'Patru')
INSERT INTO @Unitati (Cifra,Text) VALUES (5,'Cinci')
INSERT INTO @Unitati (Cifra,Text) VALUES (6,'Sase')
INSERT INTO @Unitati (Cifra,Text) VALUES (7,'Sapte')
INSERT INTO @Unitati (Cifra,Text) VALUES (8,'Opt')
INSERT INTO @Unitati (Cifra,Text) VALUES (9,'Noua')
UPDATE @Unitati SET Biti = POWER(2,Cifra)
declare @Nivele table
(
Cifra int,
Nr int, --1-singular, 2-plural
Text VARCHAR(25),
PRIMARY KEY (Cifra,Nr)
)
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (0,1|2,'')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (1,1,'UnaMie')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (1,2,'Mii')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (2,1,'UnMilion')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (2,2,'Milioane')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (3,1,'UnMiliard')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (3,2,'Miliarde')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (4,1,'UnBilion') --conform DEX http://dexonline.ro/search.php?cuv=bilioane
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (4,2,'Bilioane')
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (5,1,'UnTrilion') --conform DEX http://dexonline.ro/search.php?cuv=trilion
INSERT INTO @Nivele (Cifra,Nr,Text) VALUES (5,2,'Triloane')
declare @SuteZeciUnitati table
(
BitiSute SMALLINT,
BitiZeci SMALLINT,
BitiUnitati SMALLINT,
Text VARCHAR(25),
TextX VARCHAR(25),
PRIMARY KEY (BitiSute,BitiZeci,BitiUnitati)
)
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,0),32767,NULL)
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,0),'Zece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,1),'Unsprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,2),'Doisprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,3),'Treisprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,4),'Patrusprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,5),'Cincisprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,6),'Saisprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,7),'Saptesprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,8),'Optsprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,POWER(2,1),POWER(2,9),'Nouasprezece')
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text) VALUES (32767,32767^POWER(2,0)^POWER(2,1),POWER(2,0),NULL)
INSERT INTO @SuteZeciUnitati(BitiSute,BitiZeci,BitiUnitati,Text,TextX) VALUES (32767,32767^POWER(2,0)^POWER(2,1),32767^POWER(2,0),NULL,'Si')
DECLARE @text VARCHAR(255)
SET @text = ''
--<procesare intreg>
DECLARE @intreg VARCHAR(25),@i int
if CHARINDEX('.', @nr) = 0 begin set @nr = @nr + '.00' end
SET @intreg = case when CHARINDEX('.', @nr) = 0 then cast(SUBSTRING(@nr, 1, NULLIF(CHARINDEX('.', @nr) - 1, -1)) as varchar(50))
else cast(SUBSTRING(@nr, 1, CHARINDEX('.', @nr) - 1) as varchar(50)) end
SET @intreg = CASE WHEN LEN(@intreg) % 3 <> 0 THEN REPLICATE('0',3-(LEN(@intreg) % 3))+@intreg ELSE @intreg END
SET @i = 1
WHILE @i < LEN(@intreg)
BEGIN
--<procesare parte>
DECLARE @parte CHAR(3),@nr_nivel int
SET @parte = SUBSTRING(@intreg,@i,3)
SET @nr_nivel = LEN(@intreg)/3 - @i/3 - 1
IF ( @parte = '000' )
SET @text = @text + CASE WHEN @nr_nivel = 0 AND LEN(@intreg)=3 THEN 'Zero'ELSE '' END
ELSE
IF ( @parte = '001' AND @nr_nivel <> 0 )
SET @text = @text + (SELECT TOP 1 N.Text FROM @Nivele N WHERE N.Cifra = @nr_nivel AND Nr & 1 = 1)
ELSE
BEGIN
DECLARE @s SMALLINT, @z SMALLINT, @u SMALLINT
SET @s = POWER(2,CAST(SUBSTRING(@parte,1,1) AS int))
SET @z = POWER(2,CAST(SUBSTRING(@parte,2,1) AS int))
SET @u = POWER(2,CAST(SUBSTRING(@parte,3,1) AS int))
SET @text = @text + (SELECT TOP 1 S.Text + ISNULL(Z.Text,'') + COALESCE(SZU.Text,ISNULL(SZU.TextX,'')+U.Text) + N.Text AS Descriere
FROM @Sute AS S, @SuteZeciUnitati AS SZU, @Zeci AS Z, @Unitati AS U, @Nivele AS N
WHERE S.Biti & @s = @s
AND SZU.BitiZeci & @z = @z
AND SZU.BitiUnitati & @u = @u
AND Z.Biti & @z = @z
AND U.Biti & @u = @u
AND N.Cifra = @nr_nivel
AND N.Nr & 2 = 2)
END
--</procesare parte>
SET @i = @i + 3
END
--</procesare intreg>
RETURN @text + cast('.' as varchar(1)) + SUBSTRING(@Nr, CHARINDEX('.', @Nr) + 1, LEN(@Nr))
END
GO
multumesc.
alex.