CODEWITHSUNDEEP
mysqlsqloracle-sqldeveloper
Bogado Alejandro
Bogado Alejandro

Reputation: 105

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.

Upvotes: 0

Views: 487

Answers (1)

Beege
Beege

Reputation: 665

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.

Upvotes: 2

Related Questions