Remove Blank-Padding from To_Char() Output

Remove blank-padding from to_char() output

The pattern 'Day' is blank-padded to the right, making all days 9 characters long. Use the FM Template Pattern Modifier to remove any padding:

SELECT d::date AS day
     , to_char(d, 'yyyy-mm-dd') AS day_text
     , to_char(d, 'FMDay')      AS weekday
FROM generate_series('2013-03-01'::date
                   , '2013-03-07'::date
                   , interval '1 day') d;

Also demonstrating generate_series() for timestamps. One less query level.

If you need an actual date in the view, make it an actual type date, don't convert to text and back.

And don't use the basic type name date as column name. Using day instead.

And I would just use text for the text. No point in converting to varchar.

PostgreSQL TO_CHAR() Remove Padding on Month Field

Use the "fill mode" FM which - in contrast do its name - does not fill the value with spaces:

TO_CHAR(created_at, 'FMMonth DD, YYYY')

Why is Oracle's to_char() function adding spaces?

The format mask that you are using is fixed width and allows for a minus sign

Oracle Date TO_CHAR('Month DD, YYYY') has extra spaces in it

if you use 'Month' in to_char it right pads to 9 characters; you have to use the abbreviated 'MON', or to_char then trim and concatenate it to avoid this. See, http://www.techonthenet.com/oracle/functions/to_char.php

select trim(to_char(date_field, 'month')) || ' ' || to_char(date_field,'dd, yyyy')
  from ...

or

select to_char(date_field,'mon dd, yyyy')
  from ...  

oracle sql TO_CHAR function is adding a trailing blank in some case

It isn't adding a trailing space to the value - if you check the length it's still 18, and if it was then the concatenated version would have a space too. The metadata for the column is deciding the maximum possible length is 19, which seems to be true if the original balance value exceeds 16 digits:

set colsep ;
with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
)
SELECT 
  '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE            ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;-------------------;-;-;----------;-;---------------------
[;000000074946036600 ;];[;        18;];[000000074946036600] 
[;123456789012345600 ;];[;        18;];[123456789012345600] 
[;###################;];[;        19;];[###################]

A 17-digit value can't be displayed in the supplied format, and it substitutes hashes; but interestingly 19 of them, however large the balance value actually is.

The FM modifier suppresses a leading space for a positive value; but if you had a negative 16-digit value you would still need 19 digits to display it as the minus sign would be present. You know the value can't be negative because of the ABS() call, but the TO_CHAR() function doesn't know that, so it still has to allow for the minus sign in the column width.

If you're really running the query/conversion from an application, e.g. over JDBC, then the string you get back (for less than 17 digits, after the ABS()) will be 18 characters and won't have the (non-existent) trailing space.

Your client (or at least, SQL*Plus and SQL Developer) is formatting the column width based on what it thinks the widest possible value can be. You can override that:

column balance format A18

with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
  union all select 123456789012345678 from dual
)
SELECT 
  '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE           ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;------------------;-;-;----------;-;---------------------
[;000000074946036600;];[;        18;];[000000074946036600] 
[;123456789012345600;];[;        18;];[123456789012345600] 
[;##################;];[;        19;];[###################]
 ;#                 ; ; ;          ; ;                     

[;##################;];[;        19;];[###################]
 ;#                 ; ; ;          ; ;                     

... which makes the non-space disappear, but messes up even more if/when the original value exceeds 16 digits as it wraps the extra hash onto the next line.

You could also explicitly CAST the result to the length you expect:

clear columns

with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
  union all select 123456789012345678 from dual
)
SELECT 
  '[',CAST(TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as varchar2(18)) as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE           ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;------------------;-;-;----------;-;---------------------
[;000000074946036600;];[;        18;];[000000074946036600] 
[;123456789012345600;];[;        18;];[123456789012345600] 
[;##################;];[;        19;];[###################]
[;##################;];[;        19;];[###################]

Check for integer in string array

I found the following to provide the desired result:

IF nc_ecosite in 
(select (unnest(string_to_array(current_consite, ',')))::integer 
from current_site_record 
where current_ecosite_nc::integer = nc_ecosite) THEN
   ecosite := nc_ecosite::integer;

How to remove leading zeroes from day and month values in Oracle, when parsing to string using to_char function?

select   to_char(sysdate,'DD.MM.YY') -- Without Fill Mode
,        to_char(sysdate-20,'fmDD.fmMM.YY')  -- With Fill Mode, 20 days ago
  from dual;

Returns

21.03.14    | 1.3.14

FM Fill mode.

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.

Oracle RPAD() padding with an empty string

RPAD accepts a character or string as its 3rd parameter which is used to "pad" the initial string to a particular length.

RPAD can be used to return a string which is "guaranteed" to be n characters long (as per the 2nd parameter).

Since NULL does not represent any particular character or string and has zero length, it cannot be used for padding - RPAD apparently returns NULL in this instance, which makes sense as the only other option would be for RPAD to raise an exception.

Trigger for select function

First, you can simplify your SELECT.

select concat_ws ('/'
        , c.abreviation
        , to_char(date_op,'yy/mm')
        , to_char(compteur_op_cat, 'FM000')
       ) AS num_bord
from   operation o
join   categories c USING (id_cat);

But there are no "triggers on SELECT". You may be looking for a VIEW or a MATERIALIZED VIEW.

The key to performance with LIKE is indexing:

• PostgreSQL LIKE query performance variations

Explain

• Use concat_ws(), since you want to insert separators.

• The FM modifier avoids leading spaces for numbers.

  • Remove blank-padding from to_char() output

Aside: it's abbreviation, not abreviation.

---

Related Topics