Split (SQL Server 2005)

 UDF Split:

Input String: 1,2,3,4

Output: 1

2

3

4

Number to Word (INR)

Recursive Function in SQL

Here, I will show you how to convert numbers (in numeric format) into words (in character format). This was a requirement I received for check printing, where I needed to automatically convert an amount into words, such as 123 = One Hundred Twenty-Three.

To accomplish this, I wrote a recursive User-Defined Function (UDF) in SQL Server.

Here is the sample code:

CREATE FUNCTION {username}.Nostowords (@Nos AS NUMERIC(15, 2))
returns VARCHAR(500)
AS
  BEGIN
      DECLARE @SendNo AS INT
      DECLARE @Values AS VARCHAR(500)

      SET @values = Isnull(@Values, '')
      SET @SendNo = CONVERT (INT, @Nos)

      IF Len (@SendNo) = 1
        BEGIN
            IF @SendNo BETWEEN 0 AND 9
              BEGIN
                  IF @SendNo = 0
                    SET @Values = @Values + 'Zero'

                  IF @SendNo = 1
                    SET @Values = @Values + 'One'

                  IF @SendNo = 2
                    SET @Values = @Values + 'Two'

                  IF @SendNo = 3
                    SET @Values = @Values + 'Three'

                  IF @SendNo = 4
                    SET @Values = @Values + 'Four'

                  IF @SendNo = 5
                    SET @Values = @Values + 'Five'

                  IF @SendNo = 6
                    SET @Values = @Values + 'Six'

                  IF @SendNo = 7
                    SET @Values = @Values + 'Seven'

                  IF @SendNo = 8
                    SET @Values = @Values + 'Eight'

                  IF @SendNo = 9
                    SET @Values = @Values + 'Nine'
              END
            ELSE
              BEGIN
                  SET @Values = ''
              END
        END
      ELSE IF Len (@SendNo) = 2
        BEGIN
            IF @SendNo BETWEEN 10 AND 19
              BEGIN
                  IF @SendNo = 10
                    SET @Values = 'Ten'

                  IF @SendNo = 11
                    SET @Values = 'Eleven'

                  IF @SendNo = 12
                    SET @Values = 'Twlve'

                  IF @SendNo = 13
                    SET @Values = 'Thirteen'

                  IF @SendNo = 14
                    SET @Values = 'Fourteen'

                  IF @SendNo = 15
                    SET @Values = 'Fiveteen'

                  IF @SendNo = 16
                    SET @Values = 'Sixteen'

                  IF @SendNo = 17
                    SET @Values = 'Seventeen'

                  IF @SendNo = 18
                    SET @Values = 'Eighteen'

                  IF @SendNo = 19
                    SET @Values = 'Nineteen'
              END
            ELSE
              BEGIN
                  IF LEFT(@SendNo, 1) = 2
                    SET @Values = @Values + 'Twenty'
                  ELSE IF LEFT(@SendNo, 1) = 3
                    SET @Values = @Values + 'Thirty'
                  ELSE IF LEFT(@SendNo, 1) = 4
                    SET @Values = @Values + 'Forty'
                  ELSE IF LEFT(@SendNo, 1) = 5
                    SET @Values = @Values + 'Fifty'
                  ELSE IF LEFT(@SendNo, 1) = 6
                    SET @Values = @Values + 'Sixty'
                  ELSE IF LEFT(@SendNo, 1) = 7
                    SET @Values = @Values + 'Seventy'
                  ELSE IF LEFT(@SendNo, 1) = 8
                    SET @Values = @Values + 'Eighty'
                  ELSE IF LEFT(@SendNo, 1) = 9
                    SET @Values = @Values + 'Ninty'

                  IF @SendNo % 10 <> 0
                    SET @Values = @Values
                                  + (SELECT {username}.Nostowords(( @SendNo % 10 )))
              END
        END
      ELSE IF Len (@SendNo) = 3
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 1)))
                          + ' Hundred'

            IF ( @SendNo % 100 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 100 )))
        END
      ELSE IF Len (@SendNo) = 4
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 1)))
                          + ' Thousand'

            IF ( @SendNo % 1000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 1000 )))
        END
      ELSE IF Len (@SendNo) = 5
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 2)))
                          + ' Thousand'

            IF ( @SendNo % 1000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 1000 )))
        END
      ELSE IF Len (@SendNo) = 6
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 1)))
                          + ' Lakh'

            IF ( @SendNo % 100000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 100000 )))
        END
      ELSE IF Len (@SendNo) = 7
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 2)))
                          + ' Lakh'

            IF ( @SendNo % 100000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 100000 )))
        END
      ELSE IF Len (@SendNo) = 8
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 1)))
                          + ' Crore'

            IF ( @SendNo % 10000000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 10000000 ))
                              )
        END
      ELSE IF Len (@SendNo) = 9
        BEGIN
            SET @Values = (SELECT {username}.Nostowords(LEFT(@SendNo, 2)))
                          + ' Crore'

            IF ( @SendNo % 10000000 ) <> 0
              SET @Values = @Values
                            + (SELECT {username}.Nostowords(( @SendNo % 10000000 ))
                              )
        END

      IF CONVERT (INT, @Nos) <> Round(@Nos, 2)
        BEGIN
            SET @SendNo = Round (( @Nos - CONVERT (INT, @Nos) ), 2) * 100
            SET @Values = @Values + 'And'
                          + (SELECT {username}.Nostowords (@SendNo))
                          + 'Ps. Only'
        END

      SET @Values = Replace(@Values, 'Rs.', '')
      SET @Values = 'Rs. ' + Ltrim(@Values )
      SET @Values = Replace(@Values, 'Only', '')
      SET @Values = Rtrim(@Values) + ' Only'

      RETURN ( @Values )
  END;
GO
  Select {username}.Nostowords(152.36);
            

Hope this will help you.

Feel free to email me in case if you need some more detail on this.