How to change numbers to words in SQL?

I want to display the numbers in Words in SQL

e.g. 1204 should be displayed as One thousand and four..

Any idea?

You mean you want the results of a query to do that? Are you using VB?

Not very easily.


I wrote some code a while back for just this purpose, so you'll have to search the forums (button at top) and find it. Runs in VB/VBA.

As to Sql doing it... not heard of that, but if you do manage to make it work, post up how you did it.


Vince

I don't recall where this came from, so sorry I can't give credit where it's due.

Edit:apparently from http://www.mentalis.org/tips/tip108.shtml

Code:

---

Public Function Number_To_Words(ByVal numstr As Long) As String
    ' The best data type to feed in is Decimal, but it is up to you

    Dim tempstr As String
    Dim newstr As String
    'numstr = CInt(numstr) 'CDec(numstr)

    If numstr = 0 Then
      Number_To_Words = "zero "
      Exit Function
    End If

    If numstr > 10 ^ 24 Then
      Number_To_Words = "Too big"
      Exit Function
    End If

    If numstr >= 10 ^ 12 Then
      newstr = Number_To_Words(Int(numstr / 10 ^ 12))
      numstr = ((numstr / 10 ^ 12) - _
      Int(numstr / 10 ^ 12)) * 10 ^ 12
      If numstr = 0 Then
        tempstr = tempstr & newstr & "billion "
      Else
        tempstr = tempstr & newstr & "billion, "
      End If
    End If

    If numstr >= 10 ^ 6 Then
      newstr = Number_To_Words(Int(numstr / 10 ^ 6))
      numstr = ((numstr / 10 ^ 6) - _
      Int(numstr / 10 ^ 6)) * 10 ^ 6
      If numstr = 0 Then
        tempstr = tempstr & newstr & "million "
      Else
        tempstr = tempstr & newstr & "million, "
      End If
    End If

    If numstr >= 10 ^ 3 Then
      newstr = Number_To_Words(Int(numstr / 10 ^ 3))
      numstr = ((numstr / 10 ^ 3) - _
      Int(numstr / 10 ^ 3)) * 10 ^ 3
      If numstr = 0 Then
        tempstr = tempstr & newstr & "thousand "
      Else
        tempstr = tempstr & newstr & "thousand, "
      End If
    End If

    If numstr >= 10 ^ 2 Then
      newstr = Number_To_Words(Int(numstr / 10 ^ 2))
      numstr = ((numstr / 10 ^ 2) - _
      Int(numstr / 10 ^ 2)) * 10 ^ 2
      If numstr = 0 Then
        tempstr = tempstr & newstr & "hundred "
      Else
        tempstr = tempstr & newstr & "hundred and "
      End If
    End If

    If numstr >= 20 Then
      Select Case Int(numstr / 10)
      Case 2
      tempstr = tempstr & "twenty "
      Case 3
      tempstr = tempstr & "thirty "
      Case 4
      tempstr = tempstr & "forty "
      Case 5
      tempstr = tempstr & "fifty "
      Case 6
      tempstr = tempstr & "sixty "
      Case 7
      tempstr = tempstr & "seventy "
      Case 8
      tempstr = tempstr & "eighty "
      Case 9
      tempstr = tempstr & "ninety "
      End Select
      numstr = ((numstr / 10) - _
      Int(numstr / 10)) * 10
    End If

    If numstr > 0 Then
      Select Case numstr
      Case 1
      tempstr = tempstr & "one "
      Case 2
      tempstr = tempstr & "two "
      Case 3
      tempstr = tempstr & "three "
      Case 4
      tempstr = tempstr & "four "
      Case 5
      tempstr = tempstr & "five "
      Case 6
      tempstr = tempstr & "six "
      Case 7
      tempstr = tempstr & "seven "
      Case 8
      tempstr = tempstr & "eight "
      Case 9
      tempstr = tempstr & "nine "
      Case 10
      tempstr = tempstr & "ten "
      Case 11
      tempstr = tempstr & "eleven "
      Case 12
      tempstr = tempstr & "twelve "
      Case 13
      tempstr = tempstr & "thirteen "
      Case 14
      tempstr = tempstr & "fourteen "
      Case 15
      tempstr = tempstr & "fifteen "
      Case 16
      tempstr = tempstr & "sixteen "
      Case 17
      tempstr = tempstr & "seventeen "
      Case 18
      tempstr = tempstr & "eighteen "
      Case 19
      tempstr = tempstr & "nineteen "
      End Select
      numstr = ((numstr / 10) - Int(numstr / 10)) * 10
    End If

    Number_To_Words = tempstr
End Function    ' Number_To_Words
'################################################################################

---

If I said One Thousand Two Hundred and Four, what would you think the number to be?
1204 or 1200.4..... techically speaking the answer is 1200.4. The "and" signifies the decimal spot. So, to correctly do 1204 it is "One Thousand, Two Hundred Four." But that's not how most people speak.... But if you think about it, it makes sense.... 124 is one hundred twenty-four, not one hundred twenty and four.

But eh....

TG

VB Code:

Public Function ConvertFromNumToString(ByVal dblAmount As Double) As String
'---- Written by Vincent Buckner
'----   10 May 2004
'----
'---- Requires:
'----   A double number to convert
'----
'---- In Response to a forum post asking for this function
'----   which I thought would be fun to make :)
 
    Dim aryBig() As String, aryNum() As String, aryTens() As String
    Dim strAmount As String, strHolding As String, strLoop As String, strZeros As String
    Dim dblInAmount As Double
    Dim lngLoop As Long, lngMLoop As Long, lngAmount As Long
    Dim blnAddAmType As Boolean, blnMinus As Boolean
    
'---- UK denomination - could pass as variables..?
    Const strSep As String = "."
    Const strMain As String = "pounds"
    Const strLittle As String = "pence"
    
    On Error Resume Next
 
    ReDim aryBig(4)
    aryBig(0) = ""
    aryBig(1) = "Thousand"
    aryBig(2) = "Million"
    aryBig(3) = "Billion"
    aryBig(4) = "Trillion"
 
    ReDim aryNum(19)
    aryNum(0) = ""
    aryNum(1) = "One"
    aryNum(2) = "Two"
    aryNum(3) = "Three"
    aryNum(4) = "Four"
    aryNum(5) = "Five"
    aryNum(6) = "Six"
    aryNum(7) = "Seven"
    aryNum(8) = "Eight"
    aryNum(9) = "Nine"
    aryNum(10) = "Ten"
    aryNum(11) = "Eleven"
    aryNum(12) = "Twelve"
    aryNum(13) = "Thirteen"
    aryNum(14) = "Fourteen"
    aryNum(15) = "Fifteen"
    aryNum(16) = "Sixteen"
    aryNum(17) = "Seventeen"
    aryNum(18) = "Eighteen"
    aryNum(19) = "Nineteen"
    
    ReDim aryTens(9)
    aryTens(0) = ""
    aryTens(1) = ""
    aryTens(2) = "Twenty"
    aryTens(3) = "Thirty"
    aryTens(4) = "Fourty"
    aryTens(5) = "Fifty"
    aryTens(6) = "Sixty"
    aryTens(7) = "Seventy"
    aryTens(8) = "Eighty"
    aryTens(9) = "Ninety"
 
 
    ConvertFromNumToString = ""
    
'---- convert main section
    blnAddAmType = False
    blnMinus = False
    If dblAmount < 0 Then
        blnMinus = True
        dblAmount = 0 - dblAmount
    End If
    strAmount = Format(dblAmount, "#0" & strSep & "00")
    strAmount = Left$(strAmount, InStr(1, strAmount, strSep) - 1)
    dblInAmount = CDbl(strAmount)
    
'    lngMLoop = Round(Len(CStr(dblInAmount)) / 3) + 1
    lngMLoop = Int(Len(CStr(dblInAmount)) / 3) + IIf((Len(CStr(dblInAmount)) Mod 3) > 0, 1, 0)
    strZeros = String(lngMLoop * 3, "0")
    strAmount = Format(dblInAmount, strZeros)
    For lngLoop = 1 To lngMLoop
        strLoop = Format(CLng(Mid$(strAmount, ((lngLoop - 1) * 3) + 1, 3)), "000")
        strHolding = ""
        
'---- deal with hundreds
        If Left$(strLoop, 1) <> "0" Then
            strHolding = strHolding & IIf(Len(strHolding) > 0, " and ", "") & aryNum(CLng(Left$(strLoop, 1))) & " Hundred"
            blnAddAmType = True
        End If
        
'------------------------------------------------
'---- deal with the rest of the three digits ----
        lngAmount = CLng(Right$(strLoop, 2))
        If lngAmount > 0 Then
            Select Case lngAmount
                Case Is < 20
                    If Len(aryNum(lngAmount)) > 0 Then
                        strHolding = strHolding & IIf(Len(strHolding) > 0, " and ", "") & aryNum(lngAmount)
                    End If
                Case Else
                    strHolding = strHolding & IIf(Len(strHolding) > 0, " and ", "") & aryTens(CLng(Left$(CStr(lngAmount), 1)))
                    If CLng(Right$(CStr(lngAmount), 1)) > 0 Then
                        strHolding = strHolding & IIf(Len(strHolding) > 0, "-", "") & aryNum(CLng(Right$(CStr(lngAmount), 1)))
                    End If
            End Select
            If Len(strHolding) > 0 Then
                strHolding = strHolding & IIf(Len(aryBig(lngMLoop - lngLoop)) > 0, " ", "") & aryBig(lngMLoop - lngLoop)
                blnAddAmType = True
            End If
            ConvertFromNumToString = ConvertFromNumToString & IIf(Len(ConvertFromNumToString) > 0, ", ", "") & strHolding
        End If
    Next
    
    If blnAddAmType Then ConvertFromNumToString = ConvertFromNumToString & " " & strMain
    strHolding = ""
    
'--------------------------------------
'---- convert pence/cents whatever ----
    strAmount = Format(dblAmount, "0" & strSep & "00")
    dblInAmount = CLng(Right$(strAmount, 2))
    If dblInAmount > 0 Then
        Select Case dblInAmount
            Case Is < 20
                strHolding = strHolding & IIf(Len(ConvertFromNumToString) > 0, " and ", "") & aryNum(dblInAmount)
            Case Else
                strHolding = strHolding & IIf(Len(ConvertFromNumToString) > 0, " and ", "") & aryTens(CLng(Left$(CStr(dblInAmount), 1)))
                If Len(aryNum(CLng(Right$(CStr(dblInAmount), 1)))) > 0 Then
                    strHolding = strHolding & IIf(Len(ConvertFromNumToString) > 0, "-", "") & aryNum(CLng(Right$(CStr(dblInAmount), 1)))
                End If
        End Select
        If Len(strHolding) > 0 Then strHolding = strHolding & " " & strLittle
    
        ConvertFromNumToString = ConvertFromNumToString & strHolding
    End If
    
    If Len(ConvertFromNumToString) = 0 Then ConvertFromNumToString = "Nothing"
    If blnMinus Then ConvertFromNumToString = "Minus " & ConvertFromNumToString
End Function

Prolly needs a little tweaking for american or other langs, but hopefully should be fairly easy to do.

I was considering a pointless waste of time programming session to get this to run from text files, as in load in the lang you want and it fills the arrays as required.. but ATM ... its just that, a waste of time. I'll look into it next week :)

Vince

Thanx all of ya.. for ur replies.....I got the sql solution for that..

Select to_char(to_date(1234,'J'),'JSP') From Dual