Re: sql query substr

KG> Look into the queries below,  why the result of first two queries is
KG> starting with a blank space, and to get rid of this I have to substr
KG> from position 2 rather than 1.

 

KG> select substr(to_char(34.25,'99.99'),1,5) col1 from dual; --result is KG> ' 34.2'

to_char always leaves room for the sign. That caught me out too, back when I was new to Oracle.

KG> select substr(to_char(-4.25,'99.99'),1,5) col1 from dual; --result is KG> ' -4.2'

In this case, the issue is, I believe, that to_char leaves room for your two digits the left of the decimal, and again for the sign.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

--
http://www.freelists.org/webpage/oracle-l