Amount to word in sql function??

Question

I want amount in word using sql function. So i can use that function in view. Please help

Shakti Singh Dulawat · Answer

/******Object:UserDefinedFunction[dbo].[NumberToWordsNew] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[NumberToWordsNew](@intNumberValue INTEGER ) RETURNS VARCHAR (2000) AS BEGIN DECLARE @strNumberString VARCHAR (9) DECLARE @strReturn VARCHAR (2000) DECLARE @intUnits SMALLINT --Createtableofnumbergroups DECLARE @tblNumberGroups TABLE (Units SMALLINT Hundreds SMALLINT Tens SMALLINT ) --Handleerrorsand'quickwins' IF@intNumberValue IS NULL RETURN NULL IFISNUMERIC(@intNumberValue)=0 RETURN NULL IF@intNumberValue=0 RETURN 'ZERO' IF@intNumberValue BEGIN SET @strReturn= 'MINUS' SET @intNumberValue= ABS (@intNumberValue) END SET @intUnits=0 --Populatetableofnumbergroups WHILE(@intNumberValue%1000)>0 OR (@intNumberValue/1000)>0 BEGIN INSERT INTO @tblNumberGroups(UnitsHundredsTens) VALUES (@intUnits(@intNumberValue%1000)/100(@intNumberValue%1000)%100) SELECT @intNumberValue= CAST (@intNumberValue/1000 AS INTEGER ) SET @intUnits=@intUnits+1 END --Removelastunitadded SET @intUnits=@intUnits-1 --Concatenatetextnumberbyreadingnumbergroupsinreverseorder SELECT @strReturn= ISNULL (@strReturn '' )+ ISNULL ( ISNULL (( CASE Hundreds WHEN 1 THEN 'ONEHUNDRED' WHEN 2 THEN 'TWOHUNDRED' WHEN 3 THEN 'THREEHUNDRED' WHEN 4 THEN 'FOURHUNDRED' WHEN 5 THEN 'FIVEHUNDRED' WHEN 6 THEN 'SIXHUNDRED' WHEN 7 THEN 'SEVENHUNDRED' WHEN 8 THEN 'EIGHTHUNDRED' WHEN 9 THEN 'NINEHUNDRED' END ) '' )+ CASE WHEN (Hundreds>0 OR Units AND Tens>0 THEN 'AND' ELSE '' END + ISNULL (( CASE Tens/10 WHEN 2 THEN 'TWENTY' WHEN 3 THEN 'THIRTY' WHEN 4 THEN 'FORTY' WHEN 5 THEN 'FIFTY' WHEN 6 THEN 'SIXTY' WHEN 7 THEN 'SEVENTY' WHEN 8 THEN 'EIGHTY' WHEN 9 THEN 'NINETY' END ) '' )+ ISNULL (( CASE Tens WHEN 10 THEN 'TEN' WHEN 11 THEN 'ELEVEN' WHEN 12 THEN 'TWELVE' WHEN 13 THEN 'THIRTEEN' WHEN 14 THEN 'FOURTEEN' WHEN 15 THEN 'FIFTEEN' WHEN 16 THEN 'SIXTEEN' WHEN 17 THEN 'SEVENTEEN' WHEN 18 THEN 'EIGHTEEN' WHEN 19 THEN 'NINETEEN' END ) '' )+ COALESCE ( CASE WHEN Tens%10=1 AND Tens/10 1 THEN 'ONE' END  CASE WHEN Tens%10=2 AND Tens/10 1 THEN 'TWO' END  CASE WHEN Tens%10=3 AND Tens/10 1 THEN 'THREE' END  CASE WHEN Tens%10=4 AND Tens/10 1 THEN 'FOUR' END  CASE WHEN Tens%10=5 AND Tens/10 1 THEN 'FIVE' END  CASE WHEN Tens%10=6 AND Tens/10 1 THEN 'SIX' END  CASE WHEN Tens%10=7 AND Tens/10 1 THEN 'SEVEN' END  CASE WHEN Tens%10=8 AND Tens/10 1 THEN 'EIGHT' END  CASE WHEN Tens%10=9 AND Tens/10 1 THEN 'NINE' END  '' )+ COALESCE ( CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN 'THOUSAND' END  CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN 'MILLION' END  CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN 'BILLION' END  CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN 'TRILLION' END  '' )  '' ) FROM @tblNumberGroups ORDER BY units DESC --Getridofallthespaces WHILECHARINDEX( '' @strReturn)>0 BEGIN SET @strReturn= REPLACE (@strReturn ''  '' ) END SET @strReturn=LTRIM(RTRIM(@strReturn)) RETURN @strReturn END /******Object:UserDefinedFunction[dbo].[DSP_NumericToRupees]Script Date :04/02/201518:43:07******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[DSP_NumericToRupees](@RUPEES AS DECIMAL (302)) RETURNS VARCHAR (2000) AS BEGIN DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(11)MTEXTNVARCHAR(50)) DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(11)MTEXTNVARCHAR(50)) DECLARE @AMOUNT NUMERIC (380) DECLARE @PAISE AS INT SET @AMOUNT=FLOOR(@RUPEES) SET @PAISE= CAST ( CONVERT ( DECIMAL (100)(@RUPEES%1)*100) AS INT ) --STEP2: --NOWADDUPTHESALTANDPEPPERONTOTABLEI.E.INSERTTHEWORDSINTOIT INSERT INTO @INNTBL_01 SELECT 'One' INSERT INTO @INNTBL_01 SELECT 'Two' INSERT INTO @INNTBL_01 SELECT 'Three' INSERT INTO @INNTBL_01 SELECT 'Four' INSERT INTO @INNTBL_01 SELECT 'Five' INSERT INTO @INNTBL_01 SELECT 'Six' INSERT INTO @INNTBL_01 SELECT 'Seven' INSERT INTO @INNTBL_01 SELECT 'Eight' INSERT INTO @INNTBL_01 SELECT 'Nine' INSERT INTO @INNTBL_01 SELECT 'Ten' INSERT INTO @INNTBL_01 SELECT 'Eleven' INSERT INTO @INNTBL_01 SELECT 'Twelve' INSERT INTO @INNTBL_01 SELECT 'Thirteen' INSERT INTO @INNTBL_01 SELECT 'Fourteen' INSERT INTO @INNTBL_01 SELECT 'Fifteen' INSERT INTO @INNTBL_01 SELECT 'Sixteen' INSERT INTO @INNTBL_01 SELECT 'Seventeen' INSERT INTO @INNTBL_01 SELECT 'Eighteen' INSERT INTO @INNTBL_01 SELECT 'Nineteen' INSERT INTO @INNTBL_01 SELECT 'Twenty' --SIMILARYINSERTTHEMULTIPLES INSERT INTO @INNTBL_02 SELECT 'Ten' INSERT INTO @INNTBL_02 SELECT 'Twenty' INSERT INTO @INNTBL_02 SELECT 'Thirty' INSERT INTO @INNTBL_02 SELECT 'Forty' INSERT INTO @INNTBL_02 SELECT 'Fifty' INSERT INTO @INNTBL_02 SELECT 'Sixty' INSERT INTO @INNTBL_02 SELECT 'Seventy' INSERT INTO @INNTBL_02 SELECT 'Eighty' INSERT INTO @INNTBL_02 SELECT 'Ninety' --STEP3: ----CHECKFORTHELIMITOFTHEAMOUNTI.E.WHATISTHEPLACEVALUEOFDIGITS--LACSTHOUSANDSORHUNDREDS DECLARE @WORD VARCHAR (2000) SELECT @WORD= '' DECLARE @M_AMT01 BigInt @M_AMT02 BigInt IF@AMOUNT>=10000000 BEGIN SET @M_AMT01=@AMOUNT SELECT @AMOUNT=(@AMOUNT%10000000) SET @M_AMT01=(@M_AMT01-@AMOUNT)/10000000 DECLARE @WORD1 VARCHAR (300) SET @WORD1= '' --IF@M_AMT01 20BEGIN --SET@M_AMT02=@M_AMT01 --SET@M_AMT01=(@M_AMT01%10) --SET@M_AMT02=(@M_AMT02-@M_AMT01)/10 --SET@WORD1=(SELECT@WORD1+MTEXTFROM@INNTBL_02WHERERECNO=@M_AMT02) --END IF@M_AMT01 AND @M_AMT01 0 BEGIN SET @WORD1=( SELECT @WORD1+MTEXT FROM @INNTBL_01 WHERE RECNO=@M_AMT01) END SET @WORD=dbo.NumberToWordsNew(@M_AMT01)+ 'Crore' END IF@AMOUNT AND @AMOUNT>=100000 BEGIN SET @M_AMT01=@AMOUNT SELECT @AMOUNT=(@AMOUNT%100000) SET @M_AMT01=(@M_AMT01-@AMOUNT)/100000 SET @WORD1= '' IF@M_AMT01 AND @M_AMT01>20 BEGIN SET @M_AMT02=@M_AMT01 SET @M_AMT01=(@M_AMT01%10) SET @M_AMT02=(@M_AMT02-@M_AMT01)/10 SET @WORD1=( SELECT @WORD1+MTEXT FROM @INNTBL_02 WHERE RECNO=@M_AMT02) END IF@M_AMT01 AND @M_AMT01 0 BEGIN SET @WORD1=( SELECT @WORD1+MTEXT FROM @INNTBL_01 WHERE RECNO=@M_AMT01) END SET @WORD=@WORD+@WORD1+ 'Lakh' END IF@AMOUNT AND @AMOUNT>=1000 BEGIN SET @M_AMT01=@AMOUNT SET @AMOUNT=(@AMOUNT%1000) SET @M_AMT01=(@M_AMT01-@AMOUNT)/1000 SET @WORD1= '' IF@M_AMT01 AND @M_AMT01>20 BEGIN SET @M_AMT02=@M_AMT01 SET @M_AMT01=(@M_AMT01%10) SET @M_AMT02=(@M_AMT02-@M_AMT01)/10 SET @WORD1=( SELECT @WORD1+MTEXT+ '' FROM @INNTBL_02 WHERE RECNO=@M_AMT02) END IF@M_AMT01 AND @M_AMT01 0 BEGIN SET @WORD1=( SELECT @WORD1+MTEXT+ '' FROM @INNTBL_01 WHERE RECNO=@M_AMT01) END SET @WORD=@WORD+@WORD1+ 'Thousand' END IF@AMOUNT AND @AMOUNT>=100 BEGIN SET @M_AMT01=@AMOUNT SET @AMOUNT=(@AMOUNT%100) SET @M_AMT01=(@M_AMT01-@AMOUNT)/100 SET @WORD=( SELECT @WORD+ '' +MTEXT+ 'Hundred' FROM @INNTBL_01 WHERE RECNO=@M_AMT01) END IF@AMOUNT AND @AMOUNT>20 BEGIN SET @M_AMT01=@AMOUNT SET @AMOUNT=(@AMOUNT%10) SET @M_AMT01=(@M_AMT01-@AMOUNT)/10 SET @WORD=( SELECT @WORD+MTEXT+ '' FROM @INNTBL_02 WHERE RECNO=@M_AMT01) END IF@AMOUNT AND @AMOUNT>=1 BEGIN SET @WORD=( SELECT @WORD+MTEXT+ '' FROM @INNTBL_01 WHERE RECNO=@AMOUNT) END --Change --SET@WORD=@WORD+'rupees' SET @WORD=@WORD+ '' --STEP4: --CALCULATETHEPAISEALSO. DECLARE @WORDP VARCHAR (300) SET @WORDP= '' IF@PAISE 0 BEGIN IF@PAISE AND @PAISE>20 BEGIN DECLARE @PAISE_01 VARCHAR (300) SET @PAISE_01=@PAISE SET @PAISE=(@PAISE%10) SET @PAISE_01=(@PAISE_01-@PAISE)/10 SET @WORDP=( SELECT @WORDP+MTEXT FROM @INNTBL_02 WHERE RECNO=@PAISE_01) END IF@PAISE AND @PAISE>=1 BEGIN SET @WORDP=( SELECT @WORDP+MTEXT FROM @INNTBL_01 WHERE RECNO=@PAISE) END SET @WORD=@WORD+ 'and' +@WORDP+ 'paisa' END IF@AMOUNT>=1000000000 BEGIN SET @WORD= '' END Return UPPER (@WORD+ 'ONLY' ) END