rpad and special characters

From: Dustin Hayden <DHayden_at_sprich.com>
Date: Mon, 19 Oct 2015 14:10:31 +0000
Message-ID: <ED8913458C966249856499C923CE5C8B01621A0292_at_prd-dag-03.ad.sprich.com>

Trying the following:
select

RPAD(NVL(TO_CHAR(    record_number   ),' ')                   , 10   )     col001


,RPAD(NVL(TO_CHAR(   import_date     ,'YYYY-MM-DD'),' ')       ,     20   )     col002

,RPAD(NVL(TO_CHAR(   process_status  ),' ')                   , 1    )     col003

,RPAD(NVL(TO_CHAR(   process_date    ,'YYYY-MM-DD'),' ')       ,     20   )     col004

,RPAD(NVL(TO_CHAR(   date_last_changed    ,'YYYY-MM-DD'),' ')       ,     20     )    col005

,RPAD(NVL(TO_CHAR(   manufacturer    ),' ')                   , 3    )     col006

,RPAD(NVL(TO_CHAR(   stock_number     ),' ')                   , 12   )     col007

,RPAD(NVL(TO_CHAR(   sales_object_id ),' ')                   , 10   )     col008

,RPAD(NVL(TO_CHAR(   stripped_stock  ),' ')                   , 15   )     col009

,RPAD(NVL(TO_CHAR(   item_description     ),' ')                   , 27   )     col010

from MY_TABLE

Someone enters a 'special character' into the item_description column (like café ) , and apparently, even though I'm forcing the column to 27 characters, it's using some international multi character code for that character, which pushes it beyond 27 characters.

How can I force it to only return 27 characters, regardless of special characters?

This e-mail and any attachments, contain SP Richards confidential information that is proprietary, privileged, and protected by applicable laws. If you have received this message in error and are not the intended recipient, you should not retain, distribute, disclose or use any of this information and you should destroy this e-mail, any attachments or copies therein forthwith. Please notify the sender immediately by e-mail if you have received this e-mail in error.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 19 2015 - 16:10:31 CEST