Because length returns the value of the mask and not the actual result?

Question

Because length returns the value of the mask and not the actual result?

Navigation

#1 by (2 votes)

2

I was trying to make a pad to the left of spaces to a number, so that in my view they were all formatted (in the view they can not be formatted).

When I try the function that follows:

LENGTH (TO_CHAR(a.variacion*100,'999990.99'))

oh by chance all the numbers measure 11.

However, as noted in the following list, this is not real. So, where does Oracle get that 11 ???

11      -59.00
11        1.00
11      -76.00
11       -5.00
11      -24.00
11       37.00
11      -47.00
11      -14.00
11      -21.00
11      -50.00
11        0.00
11     6032.00
11      -64.00
11       -6.00
11      -28.00
11      -59.00

As a clarification, if I change the mask to something else, for example to TO_CHAR(a.variacion*100,'990.99') , the following comes up:

8    -59.00
8      1.00
8    -76.00
8     -5.00
8    -24.00
8     37.00
8    -47.00
8    -14.00
8    -21.00
8    -50.00
8      0.00
8   #######
8    -64.00
8     -6.00
8    -28.00
8    -59.00

So the problem is the mask .. but what does it have to do ???

oracle oracle-11g oracle-10g

asked by gbianchi 14.07.2017 в 20:44

source

1 answer

How to avoid SQL injection in this Php code and create MYSQL queues? [duplicate] Windows forms control type property window?

score 2 · Accepted Answer

This is the expected behavior for the Format Models used in the TO_CHAR function.

The 9 will replace them with spaces ( ' ' ) if they do not take any value and also add a space ahead, as you can see in this query:

SELECT LENGTH (TO_CHAR(-0.59*100,'999990.99')) Longitud,
       TO_CHAR(-0.59*100,'999990.99') Texto,
       '<'||TO_CHAR(-0.59*100,'999990.99')||'>' ConEspacios
FROM dual;

Result:

Longitud | Texto     | ConEspacios
---------|-----------|---------------
      10 |    -59.00 | <    -59.00>

If you notice, your plot corresponds to the result except that TO_CHAR has added a space of more (marked in the example with the character _ ):

 '999990.99'
<_   -59.00>

To eliminate it you must use the Fill Mode Modifier ( FM ) as indicated by the documentation :

The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format checking.

LENGTH (TO_CHAR(-0.59*100,'FM999990.99'))

However, as I explained before, if you want to keep the decimal part I advise you to change the decimal frame from 99 to 00:

LENGTH (TO_CHAR(-0.59*100,'FM999990.00'))

I'll give you the complete example:

SELECT LENGTH (TO_CHAR(-0.59*100,'FM999990.00')) Longitud,
       TO_CHAR(-0.59*100,'FM999990.00') Texto,
       '<'||TO_CHAR(-0.59*100,'FM999990.00')||'>' ConEspacios
FROM dual;

Greetings