OraFAQ Forum: SQL & PL/SQL » Space is replaced by xA0 in SQL Query

Home » SQL & PL/SQL » SQL & PL/SQL » Space is replaced by xA0 in SQL Query (Oracle 12g, SQL Plus)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Space is replaced by xA0 in SQL Query [message #683760]

Thu, 18 February 2021 17:01

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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),' ')

Report message to a moderator

Re: Space is replaced by xA0 in SQL Query [message #683763 is a reply to message #683762]

Fri, 19 February 2021 01:19

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

Report message to a moderator

Re: Space is replaced by xA0 in SQL Query [message #683764 is a reply to message #683763]

Fri, 19 February 2021 01:39

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

Report message to a moderator

Re: Space is replaced by xA0 in SQL Query [message #683766 is a reply to message #683764]

Fri, 19 February 2021 08:15

Solomon Yakobson
Messages: 3305
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.

Report message to a moderator

Re: Space is replaced by xA0 in SQL Query [message #683786 is a reply to message #683766]

Sun, 21 February 2021 16:29

srai.bi
Messages: 11
Registered: February 2021

Junior Member

Thanks SY.

I will try below and get back.

Cheers,
Sid

Report message to a moderator

Previous Topic:	Assigning one nested table to other in same position
Next Topic:	convert char to number

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 18 16:03:28 CDT 2025