Number to Words Conversion

This is something useful only as a trick or a quick hack.

How do you get from a number to the same number spelled out in words.

Here is something that works perfectly well but was not intended to be used in the way this example shows. It is a mistreatment of the to_char function based on a manipulating a date for output.

SELECT n, TO_CHAR (DATE '-4712-01-01' + (n - 1), 'jspth')
  FROM (SELECT 1721058 n
          FROM DUAL);

one million seven hundred twenty-one thousand fifty-eighth

So it does work - just don't rely on it - it is after all a to_char on a date field.

REGEXP_REPLACE

Get rid of extra spaces.

WITH t AS (SELECT 'NO MORE  EXTRA      SPACES' str FROM dual)
SELECT REGEXP_REPLACE(str, ' {2,}', ' ') str
  FROM t;

STR                 
--------------------
NO MORE EXTRA SPACES


1 row selected.

This is actually a very neat example of solving a simple problem using regular expressions. Of course regular expressions are so much more powerful, indeed books have been written about them, but that is beyond the scope of this simple example.

CUBE

I just found this script in my collection

SELECT *
  FROM (SELECT   c1, c2, c3, c4, c5, c6
          FROM (SELECT 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
                  FROM DUAL)
         GROUP BY CUBE (c1, c2, c3, c4, c5, c6));


C1 C2 C3 C4 C5 C6
               
               f
            e   
            e  f
         d      
         d     f
         d  e   
         d  e  f
      c         
      c        f
      c     e   
      c     e  f
      c  d      
      c  d     f
      c  d  e   
      c  d  e  f
   b            
   b           f
   b        e   
   b        e  f
   b     d     
   b     d     f
   b     d  e  
   b     d  e  f
   b  c         
   b  c        f
   b  c     e   
   b  c     e  f
   b  c  d      
   b  c  d     f
   b  c  d  e   
   b  c  d  e  f
a               
a              f
a           e   
a           e  f
a        d     
a        d     f
a        d  e  
a        d  e  f
a     c        
a     c        f
a     c     e  
a     c     e  f
a     c  d     
a     c  d     f
a     c  d  e   
a     c  d  e  f
a  b            
a  b           f
a  b        e   
a  b        e  f
a  b     d      
a  b     d     f
a  b     d  e   
a  b     d  e  f
a  b  c         
a  b  c        f
a  b  c     e   
a  b  c     e  f
a  b  c  d      
a  b  c  d     f
a  b  c  d  e   
a  b  c  d  e  f

and it illustrates the beauty (and complexity) of CUBE.

A very simple SQL example that shows off what is possible from something very simple.

SUBSTR

We have all used SUBSTR at some time in our varied programming careers and can be fairly justified that we get what we need from the function.

As in previous articles there is more to this simple function than meets the eye.

The basic format of all SUBSTR type functions is that they take an input string, a start position and a number of characters and return that portion of the input string from the start position to the length of the number of characters requested.

for example:

SELECT SUBSTR ('1234567890', 5, 3)
  FROM DUAL;


SUB
---
567

1 row selected.

starts at position 5 and returns the next three characters from the input string.

There are several overloaded variations on this.

To return the remaining string from position 5 to the end you would write something like

SELECT SUBSTR ('1234567890', 5)
  FROM DUAL;


SUBSTR
------
567890

1 row selected.

The last argument has been omitted, and the assumption, on the part of the programmer, is that the number of characters requested is to the end of the string.

So now what? For 99% of what we want to do this is all we want and need to know. With a little manipulation we can extract portions of any string if we know its length and have a starting position.

What we should be doing is either, having a quick dip into the manual to see if that is all there is to SUBSTR, or trying to break it by throwing assorted combinations of inputs at it. So here goes:

SELECT SUBSTR (1234567890, 5, 2)
  FROM DUAL;


SU
--
56

1 row selected.

Well it seems to handle implicit conversions of numbers into strings, how about trying some negative number inputs? Lets see if we can get the number of characters before a start postion.

SELECT SUBSTR ('1234567890', 5 , -2)
  FROM DUAL;






1 row selected.

Not quite broken, but no error thrown. So that didn't work out too well, lets now try reversing the whole idea and seeing if a negative starting position has any effect?

SELECT SUBSTR ('1234567890', -4 , 2)
  FROM DUAL;


SU
--
78

1 row selected.

So from this example we can go 4 positions back from the end of the input string to start and return the 2 characters from there onwards. Omitting the number of characters returns the remainder of the input string. This means that I can now find the last x number of characters of any length input string without knowing it's length.

As an exercise to you, try repeating the last SQL statement but replace -4 with -14.

A List of Values

How often do you need to generate a list of numbers. OK well perhaps not every day, but the technique shown here should be easily adaptable to a number of situations.

WITH tbl AS
    (SELECT     ROWNUM AS RN
           FROM DUAL
     CONNECT BY LEVEL < 10 )
SELECT *
 FROM tbl;

This will generate a result set of nine rows each row containing one of the numbers 1 - 9.

Notice I could have just written

SELECT     ROWNUM AS rn
     FROM DUAL
CONNECT BY LEVEL < 10 

This example also introduces the WITH keyword which I personally find extremely useful. Other colleagues find it annoying, though I expect that will change once they become accustomed to using it more.

Personally I like the structure using WITH enforces on my coding. I also find understanding the workings of SQL statements much easier. Maybe it's because the inner workings are broken up into more accesible chunks, with the inline view code moved out of the FROM clause, decluttering the SQL.