LTRIM and TO_CHAR…and a “Strange” Query

A query using LTRIM and TO_CHAR seemed to be behaving oddly, but soon a simple explanation presented itself.

Check out the query below. Can you tell why it gives the answer it does?

First a little background about the two built-in SQL functions used in the query.

LTRIM

LTRIM removes a specified set of characters from the left of a string. If no character set is specified, LTRIM defaults to a single blank.

SQL> -- Remove zeros from the left
SQL> -- of the string till it reaches
SQL> -- a non-zero character
SQL> select ltrim('000102.3', '0')
  2  from dual;

LTRIM
-----
102.3

TO_CHAR

TO_CHAR converts the datatype of non-text data (like NUMBER or DATE) to text in a specified format. This function is generally used to format the output data.

SQL> -- Convert a number to string
SQL> select to_char(102.3, '000000.0')
  2  from dual;

TO_CHAR(1
---------
 000102.3

The “Strange” Query

If we combine the two queries — ie use the output of TO_CHAR(102.3, ‘000000.0’) as the input of the LTRIM function in query #1, we should get the same result = 102.3, right?

Let’s try it.

SQL> -- Convert 102.3 to 000102.3 using
SQL> -- TO_CHAR, then LTRIM it to
SQL> -- remove zeroes from the left
SQL> select ltrim(
  2           to_char(102.3, '000000.0')
  3           , '0')
  4  from dual;

LTRIM(TO_
---------
 000102.3

Why didn’t the zeroes on the left get trimmed? It worked all right when we used the string ‘000102.3’ directly. Then what havoc did using TO_CHAR cause that LTRIM refused to work as we wanted?

The Solution

Look closely at the answer of the “strange” query.
It is not ‘000102.3’, it is ‘ 000102.3’. See the blank space on the left?

TO_CHAR adds a blank space for the sign, when the format is specified with ‘0’.

Applause for Ankur Thakran and N Madhu Sudhan Reddy who got that right.

Some possible solutions:

1. Use TRIM to remove the blank space after TO_CHAR, before applying LTRIM.

SQL> -- Solution I
SQL> -- TRIM before applying LTRIM
SQL> select ltrim(
  2           trim (
  3           to_char(102.3, '000000.0')
  4                )
  5           , '0')
  6  from dual;

LTRIM(TRI
---------
102.3

2. Format TO_CHAR with ‘9’ instead of ‘0’. This does not add a space.

SQL> -- Solution II
SQL> -- TO_CHAR with 9, not 0
SQL> select ltrim(
  2          to_char(102.3, '999999.9')
  3           , '0')
  4  from dual;

LTRIM(TO_
---------
    102.3

3. Add fm (format modifier). This removes unnecessary white space like the leading space.

SQL> -- Solution III
SQL> -- Format mask to remove blank space
SQL> select ltrim(
  2          to_char(102.3, 'fm000000.0')
  3           , '0')
  4  from dual;

LTRIM(TO_
---------
102.3