Home » SQL & PL/SQL » SQL & PL/SQL » Space is replaced by xA0 in SQL Query (Oracle 12g, SQL Plus)
Space is replaced by xA0 in SQL Query [message #683760] Thu, 18 February 2021 17:01 Go to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi,

I am extracting data from an Oracle table. The file generated is in ANSI (came to konw form Notepad++ Encoding option). When I open the file in notepad, I see "STUDENT FEES" (space between 2 words) but when I change the Encoding to UTF-8 space is replaced by "xA0".

The target system accepts only UTF-8. So when the record is loaded to target system it appears as "STUDENT?FEES". I tried changing the file type to UTF-8 but still no good. Special character is still loading to target system.

Below is my SQL:
DEFINE OTBDIR = &1\
DEFINE Filename= CC
DEFINE FileExt= '.txt'

SET LINESIZE 32767 
SET TRIMOUT ON 
SET ECHO OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET VERIFY OFF
SET PAGESIZE 0
SET TERMOUT OFF
SET TERM OFF
set feed off
set colsep |
SET HEADING OFF

column dt new_val file_pref 

SELECT '&Filename&FileExt' AS DT FROM DUAL;

SPOOL &OTBDIR&file_pref


SELECT "CC"||'|'||"Parent"||'|'||"Alias"||'|'||"Data Storage"||'|'||"PlanType1"||'|'||"Aggregation1"||'|'||"PlanType2"||'|'||"Aggregation2"||'|'||"PlanType3"||'|'||"Aggregation3 "||'|'||"PlanType4"||'|'||"Aggregation4"||'|'||"UDA" FROM ABC.CC_View
/

SPOOL OFF

exit 
Could you please tell me how to replace xA0 with space in output file? Thanks.

Sid

[Updated on: Thu, 18 February 2021 20:52]

Report message to a moderator

Re: Space is replaced by xA0 in SQL Query [message #683762 is a reply to message #683760] Fri, 19 February 2021 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

xA0 is not a real space, it is Windows convention (which was accepted in Unicode) for "No-Break Space".
You can use TRANSLATE function to convert it to a standard space:
TRANSLATE(<column>,chr(160),' ')
Re: Space is replaced by xA0 in SQL Query [message #683763 is a reply to message #683762] Fri, 19 February 2021 01:19 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Thanks Michel,

I implemented above solution and now when I open the extracted file in notepad++ and change Encoding to UTF-8, I don't see "xA0" but a space.

Still when the file loads it is showing as ?. Any idea what is happening. Thanks.

Sid
Re: Space is replaced by xA0 in SQL Query [message #683764 is a reply to message #683763] Fri, 19 February 2021 01:39 Go to previous messageGo to next message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Hi Michel,

I manually changed the encoding to UTF-8 and then the issue was resolved.

I tried this in the script as: SET NLS_LANG=ENGLISH_ENGLISH.AL32UTF8 sqlplus

but system is not spooling my file in UTF-8. How do we encode the file in UTF-8. Thanks.

Regards,
Sid
Re: Space is replaced by xA0 in SQL Query [message #683766 is a reply to message #683764] Fri, 19 February 2021 08:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
Registered: January 2010
Location: Connecticut, USA
Senior Member
You set NLS_LANG - that's good but it isn't enough. sqlplus writes spool file based on current window chcp, so you need to change chcp to UTF8 chcp. Try chcp 65001

SY.
Re: Space is replaced by xA0 in SQL Query [message #683786 is a reply to message #683766] Sun, 21 February 2021 16:29 Go to previous message
srai.bi
Messages: 11
Registered: February 2021
Junior Member
Thanks SY.

I will try below and get back.

Cheers,
Sid
Previous Topic: Assigning one nested table to other in same position
Next Topic: convert char to number
Goto Forum:
  


Current Time: Tue Apr 13 18:18:32 CDT 2021