In SQL Server 2005, 2008, 2012, In MS SQL, to Convert Number into English Words or Hindi Words, you need to write a function, because we do not have an inbuilt function which could do the job. To Convert in Hindi words, follow the link here.. Below is the script to create function to convert large numbers into the English words :
CREATE FUNCTION [dbo].[udf_NumberToEnglishWords](@InNumericValue NUMERIC(38,2))RETURNS VARCHAR(1000)ASBEGIN-----*********************************************************DECLARE @NumericDetails TABLE(Number INT,Word VARCHAR(20))INSERT INTO @NumericDetails SELECT 1,'One'UNION ALL SELECT 2,'Two' UNION ALL SELECT 3,'Three'UNION ALL SELECT 4,'Four' UNION ALL SELECT 5,'Five'UNION ALL SELECT 6,'Six' UNION ALL SELECT 7,'Seven'UNION ALL SELECT 8,'Eight' UNION ALL SELECT 9,'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'DECLARE @DigitDetails TABLE(DigitPlace INT,PlaceName VARCHAR(30))INSERT INTO @DigitDetails SELECT 1,'Hundred'UNION ALL SELECT 2,'Thousand' UNION ALL SELECT 3,'Million'UNION ALL SELECT 4,'Billion' UNION ALL SELECT 5,'Trillion'UNION ALL SELECT 6,'Quadrillion' UNION ALL SELECT 7,'Quintillion'UNION ALL SELECT 8,'Sextillion' UNION ALL SELECT 9,'Septillion'UNION ALL SELECT 10,'Octillion' UNION ALL SELECT 11,'Nonillion'UNION ALL SELECT 12,'Decillion' UNION ALL SELECT 13,'UnDecillion'UNION ALL SELECT 14,'DuoDecillion' UNION ALL SELECT 15,'TreDecillion'UNION ALL SELECT 16,'QuattuorDecillion'UNION ALL SELECT 17,'QuinDecillion' UNION ALL SELECT 18,'SexDecillion'UNION ALL SELECT 19,'SepDecillion' UNION ALL SELECT 20,'OctoDecillion'UNION ALL SELECT 21,'NovemDecillion'UNION ALL SELECT 22,'Vigintillion'-----**************************************************************DECLARE @StrNumber VARCHAR(60),@LargeNumber VARCHAR(60),@SmallNumber VARCHAR(10),@DecimalNumber VARCHAR(10),@Chunk VARCHAR(50),@ChunkVal INT,@TenthVal INT,@UnitVal INT,@HundVal INT,@StrFinal VARCHAR(1000),@StrLength INT,@DigitLoop INTSET @DecimalNumber = ''SET @HundVal = 0SET @StrFinal = ''SET @DigitLoop = 2SET @StrNumber = ABS(@InNumericValue)SET @StrNumber = SUBSTRING(@StrNumber,1,CASEWHEN CHARINDEX('.',@StrNumber)=0 THEN LEN(@StrNumber)ELSECHARINDEX('.', @StrNumber)-1 END)SET @StrLength = LEN(@StrNumber)IF(@StrLength > 3)BEGIN-- SET @StrFinal = RIGHT(@StrNumber,3)SET @LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)SET @StrLength = LEN(@StrNumber)WHILE LEN(@LargeNumber) > 0BEGINSET @Chunk = ''SET @HundVal = 0SET @ChunkVal = CAST(RIGHT(@LargeNumber,3) AS INT)IF (@ChunkVal >= 100)BEGINSET @HundVal = @ChunkVal/100-- SET @HundVal = CAST(LEFT(@LargeNumber,1) AS INT)SELECT @Chunk = WORD FROM @NumericDetailsWHERE NUMBER = @HundValSET @Chunk = @Chunk + ' ' + 'Hundred'-- SET @StrFinal = @Chunk + @StrFinalSET @ChunkVal = @ChunkVal%100ENDIF (@ChunkVal > 20)BEGINSET @TenthVal = (@ChunkVal/10)*10SET @UnitVal = @ChunkVal%10SELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetailsWHERE NUMBER = @TenthValSELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetailsWHERE NUMBER = @UnitValSELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetailsWHERE DigitPlace = @DigitLoopSET @StrFinal = @Chunk + ', ' + @StrFinalSET @StrLength =LEN(@LargeNumber)SET @LargeNumber=SUBSTRING(@LargeNumber,-2,@StrLength)SET @DigitLoop = @DigitLoop + 1ENDELSEBEGINSELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetailsWHERE NUMBER = @ChunkValSELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetailsWHERE DigitPlace = @DigitLoopIF(@ChunkVal > 0 OR @HundVal > 0)SET @StrFinal = @Chunk + ', ' + @StrFinalSET @StrLength =LEN(@LargeNumber)SET @LargeNumber=SUBSTRING(@LargeNumber,-2,@StrLength)SET @DigitLoop = @DigitLoop + 1ENDENDSET @StrLength = 3SET @StrFinal = RTRIM(@StrFinal)SET @StrFinal = STUFF(@StrFinal,LEN(@StrFinal),1,'')ENDIF(@StrLength = 3)BEGINSET @Chunk = ''SET @SmallNumber = RIGHT(@StrNumber,3)SET @ChunkVal = CAST(LEFT(@SmallNumber,1) AS INT)IF (@ChunkVal > 0)BEGINSELECT @Chunk=Word+' '+'Hundred' FROM @NumericDetailsWHERE NUMBER = @ChunkValIF(@StrFinal <> '')SET @StrFinal = @StrFinal +', ' + @ChunkELSESET @StrFinal = @ChunkENDSET @StrLength = 2ENDIF(@StrLength < 3 AND @StrLength > 0)BEGINSET @Chunk = ''SET @SmallNumber = RIGHT(@StrNumber,2)SET @ChunkVal = CAST(LEFT(@SmallNumber,2) AS INT)IF (@ChunkVal > 20)BEGINSET @TenthVal = (@ChunkVal/10)*10SET @UnitVal = @ChunkVal%10SELECT @Chunk = WORD FROM @NumericDetailsWHERE NUMBER = @TenthValSELECT @Chunk=@Chunk+' '+WORD FROM @NumericDetailsWHERE NUMBER = @UnitValIF(@StrFinal <> '')SET @StrFinal = @StrFinal +', ' + @ChunkELSESET @StrFinal = @ChunkENDELSE IF(@ChunkVal <= 20 AND @ChunkVal > 0)BEGINSELECT @Chunk = WORD FROM @NumericDetailsWHERE NUMBER = @ChunkValIF(@StrFinal <> '')SET @StrFinal = @StrFinal +', ' + @ChunkELSESET @StrFinal = @ChunkENDEND-----*******************************************************SELECT @StrFinal = ISNULL(@StrFinal,'')RETURN @StrFinal-----*******************************************************END
Check the output below :
SELECT dbo.[udf_NumberToEnglishWords] (82000450612) AS RetValue-- Output :Eighty Two Billion, Four Hundred Fifty Thousand, Six Hundred, Twelve
Reference: Govind Badkur(http://sqlserver20.blogspot.com)
I have read your blog its very attractive and impressive. I like your blog MSBI online course Hyderabad
ReplyDeleteits not working on decimal value... ;(
ReplyDeletesmm panel
ReplyDeleteSmm Panel
Https://isilanlariblog.com/
İnstagram Takipçi Satın Al
hirdavatciburada.com
beyazesyateknikservisi.com.tr
SERVİS
Tiktok Hile
en son çıkan perde modelleri
ReplyDeleteen son çıkan perde modelleri
minecraft premium
özel ambulans
uc satın al
yurtdışı kargo
nft nasıl alınır
lisans satın al
Good content. You write beautiful things.
ReplyDeletemrbahis
vbet
sportsbet
korsan taksi
sportsbet
mrbahis
hacklink
taksi
vbet
Good text Write good content success. Thank you
ReplyDeletetipobet
bonus veren siteler
betpark
slot siteleri
mobil ödeme bahis
kibris bahis siteleri
kralbet
poker siteleri
شركة المثالية للتنظيف بالجبيل
ReplyDeleteشركة المثالية للتنظيف بالقطيف
dijital kartvizit
ReplyDeletereferans kimliği nedir
binance referans kodu
referans kimliği nedir
bitcoin nasıl alınır
resimli magnet
VQ3EV
görüntülü.show
ReplyDeletewhatsapp ücretli show
AA2D7H
شركة مكافحة حشرات v7O73wtJmB
ReplyDelete