Number To Text Function on MS SQL (English Version)

If you need Converts a numeric value to string on the MS SQL , use the "fNumberToText" function.


Script Code:



-- Name: Convert Number to Text
-- Description:Converts a numeric value to string.
--converts numeric value to string
--handles up to 2 decimal places, with rounding
--if @isMoney = true then it will output dollars and cents
CREATE function dbo.fNumberToText (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS

BEGIN
--declare @num numeric(18,2)
--declare @isMoney bit
--set @num = 8000000000.17
--set @isMoney = 1
DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(1)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, ' One'
UNION SELECT 2, ' Two'
UNION SELECT 3, ' Three'
UNION SELECT 4, ' Four'
UNION SELECT 5, ' Five'
UNION SELECT 6, ' Six'
UNION SELECT 7, ' Seven'
UNION SELECT 8, ' Eight'
UNION SELECT 9, ' Nine'
UNION SELECT 10, ' Ten'
UNION SELECT 11, ' Eleven'
UNION SELECT 12, ' Twelve'
UNION SELECT 13, ' Thirteen'
UNION SELECT 14, ' Fourteen'
UNION SELECT 15, ' Fifteen'
UNION SELECT 16, ' Sixteen'
UNION SELECT 17, ' Seventeen'
UNION SELECT 18, ' Eighteen'
UNION SELECT 19, ' Nineteen'
UNION SELECT 20, ' Twenty'
UNION SELECT 30, ' Thirty'
UNION SELECT 40, ' Fourty'
UNION SELECT 50, ' Fifty'
UNION SELECT 60, ' Sixty'
UNION SELECT 70, ' Seventy'
UNION SELECT 80, ' Eighty'
UNION SELECT 90, ' Ninety'
SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMoney THEN treat each digit seperately (eg 11 = one one)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 char
-- acters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split
BEGIN
SET @CharText = ''
SET @HunText = ''
SET @SetText = ''
SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3)
IF len(@Numset) = 3 --Calculate ANY hundreds
BEGIN
SET @Char = left(@NumSet, 1)
SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + ' Hundred', '')
SET @SetText = @HunText
END
--look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2))
IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM tens/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ' and'
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
SET @SetText = ' and' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Quadrillion'
END + CASE WHEN len(@NumText) > 0 THEN ',' ELSE '' end
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'One'
SET @NumText = @NumText + ' Dollars'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'One'
SET @NumText = @NumText + ' Dollar'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'One'
SET @CentText = @CentText + ' Cents'
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'One'
SET @CentText = @CentText + ' Cent'
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
SET @CentText = ' and' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'Minus' + @NumText
RETURN @NumText
END