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
{ 3 comments… read them below or add one }
select to_char(102.3, ‘000000.0’)
from dual
/
The formatted output of to_char is prefixed by a blank i.e., the left
most character of the output is a blank character.
select Decode(Substr(to_char(102.3, ‘000000.0’), 1, 1), ‘ ‘,
‘Left most 1st char is blank.’,
‘Left most 1st char is not blank.’) Left_Most_Char
from dual
/
In your query ltrim is expected to remove all the ‘0’ for the left
most part of the input but only when the left most part begins with
‘0’ (trimming character) but in this case since left most character is
a blank char not a ‘0’ that’s why ltrim could not work as expected.
To make it work we can first remove any leading blanks by putting the output of to_char(102.3, ‘000000.0’) in a trim function though trim function removes blank chars from both the ends of the strings but here we are just concerned from the removal of the leading blank.
–This works as expected.
select ltrim(trim(to_char(102.3, ‘000000.0’)), ‘0’)
from dual
/
hi,
in the above query to_Char function adds a space if format is specified with ‘0’. try it with format ‘9’, which will not add a space.
select ltrim(to_char(102.3, ‘999999.9’), ‘0’) from dual;
🙂
ltrim ( substring ( to_char ([POP and Coll Release Fields Query].[Bal Prin Original], ‘999999999999990.00’ ), 1,19)) it expects number got char