Lesson Learnt - 2(PA Intel)

While Inserting Data to huge tables with many columns, always cross verify and check if the columns in the target table to which the data is getting inserted are always mapped with the correct input columns

Validate atleast 10 - 20 records after insertion

Translate Date to English Words

The Function to convert number to English words has been modified slightly to get th english words when date is given as an input.

Note: The function can still be optimized and some code can be reduced. This function is just a draft. When using this function in real time scenario some unused code has to be removed.


-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

 

-- SQL user-defined function - UDF - SQL scalar-valued function - SQL Date to text

CREATE FUNCTION fnDateToWords(@date as varchar(20))

    RETURNS VARCHAR(1024)

AS

BEGIN

         DECLARE @DATE_CONVERTED DATETIME

         SET @DATE_CONVERTED = CAST(@date AS DATETIME)

         DECLARE @MonthName VARCHAR(100), @DAY INT, @MONTH INT, @YEAR INT

         SET @DAY = DAY(@DATE_CONVERTED)

         SET @YEAR = YEAR(@DATE_CONVERTED)

         SET @MONTH = MONTH(@DATE_CONVERTED)

         declare   @date_english varchar(1024)

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

              DECLARE @day_tbl TABLE (ID int identity(0,1), Word varchar(32))

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @YEARNAME varchar(1024) =

(

  SELECT Case

    WHEN @YEAR = 0 THEN  ''

    WHEN @YEAR BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@YEAR)

   WHEN @YEAR BETWEEN 20 AND 99  

     THEN  (SELECT Word FROM @Below100 WHERE ID=@YEAR/10)+ '-' +

           dbo.fnNumberToWords( @YEAR % 10)

   WHEN @YEAR BETWEEN 100 AND 999  

     THEN  (dbo.fnNumberToWords( @YEAR / 100))+' Hundred '+

         dbo.fnNumberToWords( @YEAR % 100)

   WHEN @YEAR BETWEEN 1000 AND 999999  

     THEN  (dbo.fnNumberToWords( @YEAR / 1000))+' Thousand '+

         dbo.fnNumberToWords( @YEAR % 1000) 

          ELSE ' INVALID INPUT' END

)

SELECT @YEARNAME = RTRIM(@YEARNAME)

SELECT @YEARNAME = RTRIM(LEFT(@YEARNAME,len(@YEARNAME)-1))

                 WHERE RIGHT(@YEARNAME,1)='-'

Insert @day_tbl(Word) values

('Zero'), ('First'), ('Second'), ('Third'), ('Fourth'), ('Fifth'), ('Sixth'), ('Seventh'), ('Eighth'), ('Ninth'), ('Tenth'), ('Eleventh'), ('Thirteenth'), ('Fourteenth'), ('Fifteenth'),

('Sixteenth'), ('Seventeenth'), ('Eighteenth'), ('Ninteenth'), ('Twentieth')

DECLARE @DAYNAME VARCHAR(100)

=

(

       select case when @DAY = 30 THEN 'Thirtieth'

                     when @DAY = 31 then 'Thrity First'

                     when @day between 1 and 21

                        THEN (SELECT Word FROM @day_tbl WHERE ID=@day)

                     when @day between 21 and 30

                     then 'Tewenty '+ (SELECT Word FROM @day_tbl WHERE ID=@day)

                    

          ELSE ' INVALID DAY INPUT' END

       )

SELECT @MonthName  = DATENAME(month,@DATE_CONVERTED)

set @date_english = @DAYNAME + ' '+ @MonthName+', '+@YEARNAME

RETURN (@date_english)

END

GO

 /* Example

 SELECT DateInEnglish=dbo.fnDateToWords ('2012-01-02')

 */

Translate Numbers to Words

Use the below user - defined function to translate numbers to words:

-- SQL user-defined function - UDF - SQL scalar-valued function - SQL number to text

-- SQL numeric to words - integer to English - sql convert number to string

CREATE FUNCTION fnNumberToWords(@Number as BIGINT)

    RETURNS VARCHAR(1024)

AS

BEGIN

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

  SELECT Case

    WHEN @Number = 0 THEN  ''

    WHEN @Number BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

   WHEN @Number BETWEEN 20 AND 99  

     THEN  (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

           dbo.fnNumberToWords( @Number % 10)

   WHEN @Number BETWEEN 100 AND 999  

     THEN  (dbo.fnNumberToWords( @Number / 100))+' Hundred '+

         dbo.fnNumberToWords( @Number % 100)

   WHEN @Number BETWEEN 1000 AND 999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+

         dbo.fnNumberToWords( @Number % 1000) 

   WHEN @Number BETWEEN 1000000 AND 999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000))+' Million '+

         dbo.fnNumberToWords( @Number % 1000000)

   WHEN @Number BETWEEN 1000000000 AND 999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+

         dbo.fnNumberToWords( @Number % 1000000000)

   WHEN @Number BETWEEN 1000000000000 AND 999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+

         dbo.fnNumberToWords( @Number % 1000000000000)

  WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+

         dbo.fnNumberToWords( @Number % 1000000000000000)

  WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999  

     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+

         dbo.fnNumberToWords( @Number % 1000000000000000000)

        ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

                 WHERE RIGHT(@English,1)='-'

RETURN (@English)

END

GO

 /* Example

 SELECT NumberInEnglish=dbo.fnNumberToWords ( 6050)

*/

Link:
http://www.sqlusa.com/bestpractices2008/number-to-words/
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/de10193c-5000-42ea-84fa-7bec509ce5ab