Menu
I need to pad numbers with leading zeros (total 8 digits) for display. I'm using oracle.
select to_char(1011,'00000000') OPE_NO from dual;
select length(to_char(1011,'00000000')) OPE_NO from dual;
Instead of '00001011' I get ' 00001011'. Why do I get an extra leading blank space? What is the correct number formatting string to accomplish this?
P.S. I realise I can just use trim(), but I want to understand number formatting better.
@Eddie: I already read the documentation. And yet I still don't understand how to get rid of the leading whitespace.
@David: So does that mean there's no way but to use trim()?
438
The Oracle TRIM function is used to remove all leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then it is necessary to enclose it in single quotation marks. When no trim_character is specified, then the default value is a blank space.
Try this options : SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached. SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached. SET WRAP OFF Truncates the line if its is longer then LINESIZE.
If the column is of CHAR(n) datatype and the string you have entered does not have n characters then Oracle will pad the data with space ( CHR(32) ) characters until it has exactly n characters.
The Oracle TRIM function does not trim spaces between words. You can use a regular expression with REGEXP_REPLACE to remove occurrences of more than once space. Or, you can use the REPLACE function to remove all spaces between words – but this would result in a single long word.
Use FM (Fill Mode), e.g.
select to_char(1011,'FM00000000') OPE_NO from dual;
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
