Kokomi Kokomi -4 years ago 67
MySQL Question

rpad doesn't work correctly

I'm doing a quite easy query (rpad):

SELECT
RPAD(COMERCIO,40,' ')
FROM
SIEBEL.transacciones_acumulaciones_vm
WHERE ESTADO = 'Procesado'
AND NUMERO in ( '20232385',
'20225479',
'20265516');


Output:

RESTAURANTE EL QUINC (lenght 40)
MC DONALD (lenght 9)
CARREFOUR EXPRESS-PA (lenght 40)


The main problem here is that Almost every 1.000.000 rows a literal from a specific column doesn't add that spaces.

Answer Source

Your data probably has hidden characters. In unix, I look at a flat file using the cat command:

$ cat -vet my-file ... which displays the extended and non-printing characters. (For example, a 'tab' appears as a ^T in the output.)

With oracle, here's one way of highlighting non-printing characters in a text field:

select replace(regexp_replace(COMERCIO,'[^[:print:]]', '!ACK!'),' ','.') as CAT_VET
from SIEBEL.transacciones_acumulaciones
where   ESTADO  = 'Procesado'
AND NUMERO in ( '20232385',
  '20225479', 
  '20265516');

... this will display the COMERCIO values, replacing any non-printing characters with the tag !ACK! any spaces with a period character.

This doesn't tell you which characters are the problem, but it does provide guidance/verification of the problem.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download