RE: rpad and special characters

From: Powell, Mark <mark.powell2_at_hpe.com>
Date: Mon, 19 Oct 2015 16:50:30 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD13065AEE3_at_G9W0741.americas.hpqcorp.net>

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

Do you want 27 characters or 27 bytes?
What is the database character set?
What is the session character set?
What is the session setting for NLS_LENGTH_SEMANTICS?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dustin Hayden Sent: Monday, October 19, 2015 10:11 AM
To: Oracle-L_at_FreeLists.org
Subject: rpad and special characters

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 - 18:50:30 CEST