Diana:

...daca insa lucrezi pe SQL 2000...

- "traduci" algoritmul lui Bogdan in TSQL (nu mi-ar placea sa fac asta...)

sau

-  daca este posibil iti "faci treaba" intr-o aplicatie .NET, care va "sti" mai bine sa prelucreze siruri de caractere.

 

Necesitatea unui astfel de algoritm apare de regula atunci cand

[1] se doreste tiparirea pe un document (pe o chitanta de exemplu) a respectivului text

sau

[2] se doreste afisarea intr-un formular a respectivului text.

Solutia cea mai buna este - cred si eu -  implementarea intr-un limbaj de programare disponibil pe platforma .Net (C#, VB.Net, etc.). 

Daca totusi se doreste implementarea in T-SQL (solutie pe care NU o recomand) se poate alege o solutie putin diferita, solutie orientata pe procesarea datelor din tabele (ca sa zic asa):

 

CREATE TABLE Sute

(

      Cifra TINYINT,

      Text VARCHAR(25),

      Biti SMALLINT

)

GO

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')

GO

UPDATE Sute SET Biti = POWER(2,Cifra)

GO

 

CREATE TABLE Zeci

(

      Cifra TINYINT,

      Text VARCHAR(25),

      Biti SMALLINT

)

GO

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')

GO

UPDATE Zeci SET Biti = POWER(2,Cifra)

GO

 

CREATE TABLE Unitati

(

      Cifra TINYINT PRIMARY KEY,

      Text VARCHAR(25),

      Biti SMALLINT

)

GO

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')

GO

UPDATE Unitati SET Biti = POWER(2,Cifra)

GO

 

CREATE TABLE Nivele

(

      Cifra TINYINT,

      Nr TINYINT, --1-singular, 2-plural

      Text VARCHAR(25),

      CONSTRAINT cp_nivele PRIMARY KEY (Cifra,Nr)

)

GO

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')

GO

 

CREATE TABLE SuteZeciUnitati

(

      BitiSute    SMALLINT,

      BitiZeci    SMALLINT,

      BitiUnitati SMALLINT,  

      Text              VARCHAR(25),

      TextX             VARCHAR(25),

      CONSTRAINT cp_sutezeciunitati PRIMARY KEY (BitiSute,BitiZeci,BitiUnitati)

)

GO

 

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')

GO

 

CREATE FUNCTION Nr2Text( @nr INT )

RETURNS VARCHAR(255)

BEGIN

      DECLARE @text VARCHAR(255)

      SET @text = ''

      --<procesare intreg>

      DECLARE @intreg VARCHAR(25),@i TINYINT

      SET @intreg = CAST(@nr AS VARCHAR(25))

      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 TINYINT

            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 TINYINT))

                              SET @z = POWER(2,CAST(SUBSTRING(@parte,2,1) AS TINYINT))

                              SET @u = POWER(2,CAST(SUBSTRING(@parte,3,1) AS TINYINT))

 

                              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

END

GO

 

SELECT dbo.Nr2Text(1234567) AS Exemplu

--UnMilionDouaSuteTreiZeciSiPatruMiiCinciSuteSaizeciSiSapte