How can I get string format for a number?
Thursday, September 27th, 2007Q: How can I get string format for a number? For example I’d like to print 123 in strings like “one hundered twenty three”.
Bryan Oliver says: OK, you asked for it – this one is somewhat long (anybody have a more brief suggestion?):
–**************************************
–
– Name: A Number To Words function Version 2
– Description:Converts a numeric value to words, i.e, 123
– will return ONE HUNDRED TWENTY THREE
–
– Inputs:@num – a numeric value
–
– Returns:The number in words
– **************************************
– Assumes:Copy and paste the code into
– Query Analyzer and hit F5, then you can
– use it just as you would use other SQL functions.
– **************************************
–
CREATE FUNCTION NumToWords_Ver2(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
DECLARE @nullStr int
SET @res = ”
DECLARE @tblNum TABLE(Num int, NumStr varchar(20))
INSERT INTO @tblNum
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’
DECLARE @hundred varchar(200)
SET @hundred = ”
DECLARE @nStr varchar(20)
SET @place = @len
WHILE @place > 0
BEGIN
SET @place = @place – 1
SET @nStr = NULL
SET @digit = SUBSTRING(@cNum, @len-@place, 1)
IF (@place+1) % 3 = 1 –One’s place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE @place / 3
WHEN 0 THEN ”
WHEN 1 THEN ‘ THOUSAND’
WHEN 2 THEN ‘ MILLION’
WHEN 3 THEN ‘ BILLION’
WHEN 4 THEN ‘ TRILLION’
WHEN 5 THEN ‘ QUADRILLION’
WHEN 6 THEN ‘ QUINTILLION’
END
SET @hundred = ”
END
IF (@place+1) % 3 = 0 –Hundred’s place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr + ‘ HUNDRED’
SET @tens = SUBSTRING(@cNum, @len-@place+1, 2)
IF LEN(@hundred) > 0 AND (@tens = ” OR @tens = ‘00′)
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ”
WHEN 1 THEN ”
WHEN 2 THEN ‘ THOUSAND’
WHEN 3 THEN ‘ MILLION’
WHEN 4 THEN ‘ BILLION’
WHEN 5 THEN ‘ TRILLION’
WHEN 6 THEN ‘ QUADRILLION’
WHEN 7 THEN ‘ QUINTILLION’
END
ELSE
SET @res = @res + @hundred
SET @hundred = ”
END
ELSE IF (@place+1) % 3 = 2 –Ten’s place
BEGIN
SET @tens = SUBSTRING(@cNum, @len-@place, 2)
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
IF @nStr IS NULL
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10
SET @digit = SUBSTRING(@cNum, @len-@place+1, 1)
SELECT @nStr = @nStr + NumStr FROM @tblNum WHERE Num = @digit
END
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ”
WHEN 1 THEN ‘ THOUSAND’
WHEN 2 THEN ‘ MILLION’
WHEN 3 THEN ‘ BILLION’
WHEN 4 THEN ‘ TRILLION’
WHEN 5 THEN ‘ QUADRILLION’
WHEN 6 THEN ‘ QUINTILLION’
END
SET @place = @place – 1
SET @hundred = ”
END
END
RETURN @res
END
Technorati Tags:
SQL Server, value conversion, T-SQL, programming