Va salut, stimati prieteni!
Pentru un prim post as vrea sa postez o functie pe care am cautat-o dar nu am gasit o solutie 100% buna, si din punct de vedere gramatical, prin care valorile de pe o chitanta si nu numai sa fie transformate in litere.
Pornind de la o idee gasita tot pe acest site (parca, nu mai stiu, am cautat in mai multe locuri) am creat functia aceasta:
(este copiata direct din serverul meu)
Sper sa ajute pe cineva!
/****** Object: UserDefinedFunction [dbo].[mvf_TraducereValori] Script Date: 06/10/2013 22:37:57 ******/
/****** Se utilizeaza astfel: SELECT dbo.mvf_TraducereValori(435678243827109809.99) sau in loc de valoare
puneti un camp dintr-o tabela. Valoarea zecimalelor este tradusa doar pentru primele 2 cifre ******/
/****** Realizat de Vali Maties ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[mvf_TraducereValori] (@valoare numeric(32,2))
returns varchar(300)
as
begin
-------------------------------------------------------------------------------------------
declare @strval nvarchar(32),@lungime int=0,@retu varchar(300),@nivele int=0,@n1 int=0,@n2 int=0,@n3 int=0,
@i int=0,@nivel varchar(3)
set @strval=LTRIM(RTRIM(cast(cast(@valoare as bigint) as varchar(32))))
if @valoare=0
begin
set @retu='ZeroLei'
return @retu
end
declare @nivele_t table (nivel int,poz int,sg varchar(10),plural varchar(10))
insert into @nivele_t values
(1,1,'',''),
(1,2,'Zece','Zeci'),
(1,3,'Suta','Sute'),
(2,1,'Mie','Mii'),
(2,2,'Zece','Zeci'),
(2,3,'Suta','Sute'),
(3,1,'Milion','Milioane'),
(3,2,'Zece','Zeci'),
(3,3,'Suta','Sute'),
(4,1,'Miliard','Miliarde'),
(4,2,'Zece','Zeci'),
(4,3,'Suta','Sute'),
(5,1,'Bilion','Bilioane'),
(5,2,'Zece','Zeci'),
(5,3,'Suta','Sute'),
(6,1,'Biliard','Biliarde'),
(6,2,'Zece','Zeci'),
(6,3,'Suta','Sute')
declare @gfem table(id int,nume varchar(10))
insert into @gfem values
(1,'O'),
(2,'Doua'), (3,'Trei'),
(4,'Patru'), (5,'Cinci'),
(6,'Sase'), (7,'Sapte'),
(8,'Opt'), (9,'Noua')
declare @gmasc table(id int,nume varchar(10))
insert into @gmasc values
(1,'Unu'),
(2,'Doi'), (3,'Trei'),
(4,'Patru'), (5,'Cinci'),
(6,'Sase'), (7,'Sapte'),
(8,'Opt'), (9,'Noua')
declare @gneu table(id int,nume varchar(10))
insert into @gneu values
(1,'Unu'),
(2,'Doua'), (3,'Trei'),
(4,'Patru'), (5,'Cinci'),
(6,'Sase'), (7,'Sapte'),
(8,'Opt'), (9,'Noua')
declare @ex table (id int,nume varchar(10))
insert into @ex values
(1,'Un'), (4,'Pai'),
(6,'Sai')
set @nivele=ceiling(LEN(@strval)/3.0)
if @nivele>(select max(nivel) nivel from @nivele_t)
begin
set @retu='Valoare mult prea mare!'
return @retu
end
set @retu=''
set @strval=REPLICATE('0',(@nivele*3)-len(ltrim(rtrim(@strval))))+LTRIM(@strval)
while @i<@nivele
begin
set @i=@i+1
select @nivel=LEFT(RIGHT(@strval,3*((@nivele-@i)+1)),3)
set @n1=SUBSTRING(@nivel,1,1)
set @n2=SUBSTRING(@nivel,2,1)
set @n3=SUBSTRING(@nivel,3,1)
select @retu=@retu+
case
when @n1<>0 and @n2<>0 and @n3<>0 then
(select nume from @gfem where id=@n1)+
(Select case when @n1=1 then sg else plural end
from @nivele_t where nivel=(@nivele-@i)+1 and poz=3)+
case when @n2=1 then case when @n3 in (1,4,6) then (select nume from @ex where id=@n3)
else case when @i<>@nivele then
(select nume from @gneu where id=@n3)
else (select nume from @gmasc where id=@n3) end end +'sprezece'
when @n2=6 then (select nume from @ex where id=@n2)+'zeciSi'+
(select nume from @gneu where id=@n3)
else (select nume from @gfem where id=@n2)+'zeciSi'+
case when @nivele>1 and (@nivele-@i)+1<>1 then
(select nume from @gneu where id=@n3) else
(select nume from @gmasc where id=@n3)
end
end+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1<>0 and @n2=0 and @n3<>0 then
(select nume from @gfem where id=@n1)+
(Select case when @n1=1 then sg else plural end
from @nivele_t where nivel=(@nivele-@i)+1 and poz=3)+
(select nume from @gneu where id=@n3)+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1<>0 and @n2<>0 and @n3=0 then
(select nume from @gfem where id=@n1)+
(Select case when @n1=1 then sg else plural end
from @nivele_t where nivel=(@nivele-@i)+1 and poz=3)+
case when @n2=1 then 'Zece'
when @n2=6 then (select nume from @ex where id=@n2)+'zeci'
else (select nume from @gfem where id=@n2)+'zeci' end+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1=0 and @n2<>0 and @n3<>0 then
case when @n2=1 then case when @n3 in (1,4,6) then (select nume from @ex where id=@n3)
else case when @i<>@nivele then
(select nume from @gneu where id=@n3)
else (select nume from @gmasc where id=@n3) end end +'sprezece'
when @n2=6 then (select nume from @ex where id=@n2)+'zeciSi'+
case when @i=@nivele then (select nume from @gmasc where id=@n3) else
(select nume from @gneu where id=@n3) end
else (select nume from @gfem where id=@n2)+'zeciSi'+
case when @i=@nivele then (select nume from @gmasc where id=@n3) else
(select nume from @gneu where id=@n3) end end+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1=0 and @n2<>0 and @n3=0 then
case when @n2=1 then 'Zece'
when @n2=6 then (select nume from @ex where id=@n2)+'zeci'+
(select nume from @gneu where id=@n3)
else (select nume from @gfem where id=@n2)+'zeci' end+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1=0 and @n2=0 and @n3<>0 then
case when @i=nivele then
case when @n3<>1 then (select nume from @gmasc where id=@n3) else
(select nume from @ex where id=@n3) end else
(select nume from @gfem where id=@n3)+
(select case when @n3=1 then sg else plural end
from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1<>0 and @n2=0 and @n3=0 then
(select nume from @gfem where id=@n1)+
(Select case when @n1=1 then sg else plural end
from @nivele_t where nivel=(@nivele-@i)+1 and poz=3)+
(select plural from @nivele_t where nivel=(@nivele-@i)+1 and poz=1)
when @n1=0 and @n2=0 and @n3=0 then ''
end
end
set @retu=@retu+case when @valoare=1 then 'Leu' else 'Lei' end
-- Pana aici s-a tradus partea intreaga
set @strval=right(CAST(@valoare as varchar(40)),2)
if CAST(@strval as numeric(2,0))<>0
begin
set @n1=SUBSTRING(@strval,1,1)
set @n2=SUBSTRING(@strval,2,1)
select @retu=@retu+'Si'+
case
when @n1=0 and @n2<>0
then case when @n2=1 then
(select nume from @ex where id=@n2)+'Ban'
else
(select nume from @gmasc where id=@n2)+'Bani'
end
when @n1<>0 and @n2=0
then case when @n1=1 then 'Zece'
when @n1=6 then (select nume from @ex where id=@n1)+'zeci'
else (select nume from @gfem where id=@n1)+'zeci' end+'Bani'
when @n1<>0 and @n2<>0
then case
when @n1=1 then
case
when @n2 in (1,4,6,7) then
(select nume from @ex where id=@n2)+'sprezeceBani'
else (select nume from @gmasc where id=@n2)+'sprezeceBani'
end
when @n1=6 then (select nume from @ex where id=@n1)+'zeciSi'+
(select nume from @gmasc where id=@n2)+'Bani'
else (select nume from @gfem where id=@n1)+'zeciSi'+
(select nume from @gmasc where id=@n2)+'Bani'
end
end
end
return @retu
-------------------------------------------------------------------------------------------
end
GO
Un mic programator!