تبدیل عدد به حروف در sql

create FUNCTION dbo.Num_ToWords (
 
    @Number Numeric (38, 0) -- Input number with as many as 18 digits
 
) RETURNS VARCHAR(8000)
 
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number int, word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3)
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
DECLARE @And nvarchar(3)
DECLARE @Neg nvarchar(10)
 
    IF @Number = 0 return 'صفر'
    
    IF  Left(@Number ,1) <> '-'
        SET  @Neg = ' '
    ELSE
    BEGIN
        SET  @Neg = 'منفی '
        SET  @Number = @Number * -1
    END
 
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3
 
 
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
 
 
INSERT INTO @NumbersTable   SELECT 0, ''
    UNION ALL SELECT 1, ' یک '      UNION ALL SELECT 2, ' دو '
    UNION ALL SELECT 3, ' سه '    UNION ALL SELECT 4, ' چهار '
    UNION ALL SELECT 5, ' پنج '     UNION ALL SELECT 6, ' شش '
    UNION ALL SELECT 7, ' هفت '    UNION ALL SELECT 8, ' هشت '
    UNION ALL SELECT 9, ' نه '     UNION ALL SELECT 10, ' ده '
    UNION ALL SELECT 11, ' یازده '   UNION ALL SELECT 12, ' دوازده '
    UNION ALL SELECT 13, ' سیزده ' UNION ALL SELECT 14, ' چهارده '
    UNION ALL SELECT 15, ' پانزده '  UNION ALL SELECT 16, ' شانزده '
    UNION ALL SELECT 17, ' هفده ' UNION ALL SELECT 18, ' هیجده '
    UNION ALL SELECT 19, ' نوزده ' UNION ALL SELECT 20, ' بیست '
    UNION ALL SELECT 30, ' سی '   UNION ALL SELECT 40, ' چهل '
    UNION ALL SELECT 50, ' پنجاه '    UNION ALL SELECT 60, ' شصت '
    UNION ALL SELECT 70, ' هفتاد '  UNION ALL SELECT 80, ' هشتاد '
    UNION ALL SELECT 90, ' نود '   UNION ALL SELECT 100, ' صد '
    UNION ALL SELECT 200, ' دویست '   UNION ALL SELECT 300, ' سیصد '
    UNION ALL SELECT 400, ' چهارصد '   UNION ALL SELECT 500, ' پانصد '
    UNION ALL SELECT 600, ' ششصد '   UNION ALL SELECT 700, ' هفتصد '
    UNION ALL SELECT 800, ' هشتصد '   UNION ALL SELECT 900, ' نهصد '
 
 
WHILE @counter <= @loops BEGIN
 
    SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
    
 
    IF @chunk <> '000' BEGIN
        SELECT @tensones = SUBSTRING(@chunk, 2, 2)
             , @hundreds = SUBSTRING(@chunk, 1, 1)
             , @tens = SUBSTRING(@chunk, 2, 1)
             , @ones = SUBSTRING(@chunk, 3, 1)
 
        IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
                if len(@outputString)>0
                    begin
                    set @And='و '
                    end
                else
                    begin
                    set @And=''
                    end
                    
                SET @outputString = (SELECT word
                                    FROM @NumbersTable
                                    WHERE @hundreds+'00'  = number)
                            + case @hundreds when '0' then '' else
                                case @tensones when '00' then '' else 'و' end  end+
                            (
                                    SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' هزار ' WHEN 3 THEN ' میلیون '
                       WHEN 4 THEN ' میلیارد '  WHEN 5 THEN ' بیلیون '
                       WHEN 6 THEN ' بیلیارد ' WHEN 7 THEN ' کوانتیلیون '
                       WHEN 8 THEN ' سکستیلیون '  WHEN 9 THEN ' سپتیلیون '
                       WHEN 10 THEN ' اکتیلیون '  WHEN 11 THEN ' نونیلیون '
                       WHEN 12 THEN ' دسیلیون '  WHEN 13 THEN ' اندسیلیون '
                       ELSE '' END
                               + @And + @outputString
            END
 
         ELSE BEGIN
 
                    if len(@outputString)>0
                    begin
                    set @And='و '
                    end
                else
                    begin
                    set @And=''
                    end
                    
                
 
                SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @hundreds+'00'  = number)
                             + case @hundreds when '0' then '' else  'و' end
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE   @tens+'0'  = number)
                             + 'و'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE  @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' هزار ' WHEN 3 THEN ' میلیون '
                       WHEN 4 THEN ' میلیارد '  WHEN 5 THEN ' بیلیون '
                       WHEN 6 THEN ' بیلیارد ' WHEN 7 THEN ' کوانتیلیون '
                       WHEN 8 THEN ' سکستیلیون '  WHEN 9 THEN ' سپتیلیون '
                       WHEN 10 THEN ' اکتیلیون '  WHEN 11 THEN ' نونیلیون '
                       WHEN 12 THEN ' دسیلیون '  WHEN 13 THEN ' اندسیلیون '
                       ELSE '' END
                            + @And + @outputString
        END
        
 
    END
 
    SELECT @counter = @counter + 1
         , @position = @position - 3
 
END
 
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
SET @outputstring = @Neg + @outputstring
 
RETURN @outputString
END
GO
 
GRANT EXEC on dbo.Num_ToWords TO PUBLIC
GO
 
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON