Hello readers, In this tutorial, I am going to create a User Defined Function to convert Number into Word.

## User Defined Function to Convert Number into Word:

Suppose you have a requirement where you have to show Amount of some kind into both Number and Word. I am creating a User Defined Function that will Convert Number into Word.

```CREATE FUNCTION dbo.udfNumberToWord (

@pNumber Numeric (38, 0)

) RETURNS VARCHAR(8000)

AS BEGIN
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
IF @pNumber = 0 Return 'Zero'
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @pNumber)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros
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 twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END

-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END

SELECT @counter = @counter + 1
, @position = @position - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
RETURN @outputString -- return the result
END
GO

GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO```

### Test the Function:

` SELECT dbo.udf_Num_ToWords(19888122)`

Thank You.

### Explore post tags

• WiFi Password Hacking is the most common term that is used for internet connection nowadays. If you want to connect to the internet on your computer or Android device then you have to need a WiFi connection. Internet is very important for android users to download anything from different websites. You can open any website or download anything from the internet with the help of the internet.

• ed pills online e d canada pharmacy online

• Next time I read a blog, I hope that it does not fail me as much as this particular one. After all, I know it was my choice to read through, however I genuinely believed you’d have something helpful to talk about. All I hear is a bunch of complaining about something you can fix if you weren’t too busy searching for attention.

• Pretty nice post. I just stumbled upon your weblog and wanted to say that I’ve really enjoyed surfing around your blog posts. After all I will be subscribing to your rss feed and I hope you write again soon!

• The very next time I read a blog, Hopefully it doesn’t fail me as much as this particular one. After all, Yes, it was my choice to read through, nonetheless I genuinely believed you would probably have something useful to say. All I hear is a bunch of whining about something you could possibly fix if you weren’t too busy searching for attention.
http://site-sell.ir/author/sabrina30f8/

• It’s onerous to seek out educated people on this subject, however you sound like you understand what you’re speaking about! Thanks

• Hello.This post was extremely motivating, particularly because I was looking for thoughts on this issue last couple of days.

• May I simply just say what a relief to discover someone who genuinely understands what they’re discussing on the net. You actually realize how to bring an issue to light and make it important. A lot more people need to look at this and understand this side of the story. It’s surprising you are not more popular because you definitely have the gift.
http://gregory602xl.blog2learn.com/44258759/options

• Can I just say what a comfort to uncover someone who truly knows what they’re discussing on the web. You definitely understand how to bring an issue to light and make it important. More people need to read this and understand this side of the story. It’s surprising you are not more popular since you certainly possess the gift.
http://josephw369man8.blogoscience.com/profile

• Good site! I really love how it is simple on my eyes and the data are well written. I am wondering how I might be notified when a new post has been made. I have subscribed to your RSS which must do the trick! Have a great day!

• May I just say what a relief to uncover an individual who genuinely understands what they’re discussing on the internet. You certainly realize how to bring an issue to light and make it important. More people really need to look at this and understand this side of your story. I was surprised you’re not more popular because you definitely have the gift.
http://englandu493ezv3.blogacep.com/profile

• Wow! This blog looks exactly like my old one! It’s on a entirely different topic but it has pretty much the same layout and design. Excellent choice of colors!

• Good post. I learn something totally new and challenging on websites I stumbleupon every day. It’s always useful to read cara hack mesin jackpot articles from other writers and practice a little something from other web sites.

• order prescriptions online without doctor cialis daily canada pharmacies without script

• Incredible points. Solid arguments. Keep up the good effort.|

• Great Ã± I should definitely pronounce, impressed with your site. I had no trouble navigating through all the tabs and related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, site theme . a tones way for your client to communicate. Nice task..

• Keep functioning ,impressive job!

• Thank you a lot for sharing this with all people you really recognise what you’re talking approximately! Bookmarked. Kindly additionally visit my website =). We could have a hyperlink alternate agreement among us!

• Spot on with this write-up, I absolutely feel this site needs a lot more attention. Good post. I learn something totally new and challenging on sites I stumbleupon every day. It’s always exciting to read trik bermain slot articles from other writers and practice something from other websites. I’ll probably be returning to read through more, thanks for the information!

• Great website you have here but I was curious if you knew of any user discussion forums that cover the same topics talked about in this article? I’d really like to be a part of online community where I can get feedback from other knowledgeable individuals that share the same interest. If you have any suggestions, please let me know. Cheers!

• I’ve been absent for some time, but now I remember why I used to love this blog. Thanks , I will try and check back more often. How frequently you update your web site?

• Major thankies for the article post.Really looking forward to read more. Awesome.

• I really enjoy the article.Really looking forward to read more. Want more.

• Wow, great post.Really looking forward to read more. Really Cool.

• I really enjoy the article.Much thanks again.

• Thanks for sharing, this is a fantastic article post.Much thanks again.

• Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a bit, but other than that, this is magnificent blog. A great read. I will certainly be back.

• I am so grateful for your blog.Thanks Again.

• It’s really a great and helpful piece of information. I am happy that you shared this helpful information with us. Please keep us up to date like this. Thanks for sharing.