| Unable to remove hidden character from field [message #498362] |
Wed, 09 March 2011 15:22  |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
Hi All,
I ran into the following issue as mention below.
select dump(column_name) from table where column_name2 = 'HP1';
dump(column_name)
--------------------------------------------------------------------------------
Typ=1 Len=5: 194,160,82,88,66
I am trying to get right of these hidden character 194 and 160. i tried different method as mention below, but no luck.
1) translate(column_name, chr(194)|| chr(16),'')
Any help would be appreciated.
Thanks
Sid
|
|
|
|
|
|
| Re: Unable to remove hidden character from field [message #498368 is a reply to message #498364] |
Wed, 09 March 2011 15:55  |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
Sorry i forgot to mention earlier that it didn't work with replace either.
select dump(replace(column_name, chr(194)||chr(160),'')) from table where column_name2 = 'HP1';
dump(column_name)
--------------------------------------------------------------------------------
Typ=1 Len=5: 194,160,82,88,66
|
|
|
|
| Re: Unable to remove hidden character from field [message #498370 is a reply to message #498368] |
Wed, 09 March 2011 16:02 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Works for me, so I assume what you say you do is still not what you really do. Post EXACTLY what you do like this:
SQL> CREATE TABLE test_tab(col VARCHAR2(20));
Table created.
SQL> INSERT INTO test_tab VALUES (
2 chr(194)||chr(160)||chr(82)||chr(88)||chr(66)
3 );
1 row created.
SQL> SELECT Dump(col) FROM test_tab;
DUMP(COL)
-------------------------------------------------
Typ=1 Len=5: 194,160,82,88,66
SQL> SELECT Dump(REPLACE(col,chr(194)||chr(160),'')) FROM test_tab;
DUMP(REPLACE(COL,CHR(194)||CHR(160),''))
--------------------------------------------------
Typ=1 Len=3: 82,88,66
SQL> DROP TABLE test_tab;
Table dropped.
SQL>
Oh, and it's also impossible to get a column alias "dump(column_name)" while selecting "dump(replace(column_name, chr(194)||chr(160),''))" without an alias. Faking results usually is a sure way of never arriving at a solution.
[Updated on: Wed, 09 March 2011 16:06] Report message to a moderator |
|
|
|
| Re: Unable to remove hidden character from field [message #498377 is a reply to message #498370] |
Wed, 09 March 2011 16:32 |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
Actually, I have some data is getting loading from Excel sheet and it is in large number. So, it is not that i am trying to create myself and then removing this character but it is getting loading from Excel sheet.
But i did try your test case and try to create a record with 194 and 160 value embeded init. Surprisingly, after inserting the records when i am querying the table i don't see those character in the result set as mention below.
SQL> create table table_1 (column_name varchar2(15), column_name2 varchar2(15));
Table created
SQL> insert into table_1 VALUES (chr(194)||chr(160)||chr(82)||chr(88)||chr(66),'HP1');
1 row inserted
SQL> COMMIT;
Commit complete
SQL> select dump(column_name) from table_1 where column_name2 = 'HP1';
DUMP(COLUMN_NAME)
--------------------------------------------------------------------------------
Typ=1 Len=3: 82,88,66
SQL> select * from table_1;
COLUMN_NAME COLUMN_NAME2
--------------- ---------------
RXB HP1
SQL>
i think it gotta be some other problem or might have to do with database character set?
BTW, below is the information for database character set.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
Peace,
Thanks
Sid
[Updated on: Wed, 09 March 2011 16:37] Report message to a moderator |
|
|
|
| Re: Unable to remove hidden character from field [message #498381 is a reply to message #498377] |
Wed, 09 March 2011 16:50 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
OK, that's an entirely different problem.
1) AL32UTF8 is a multi-byte character set. So basically one to four numbers in the dump can mean one character.
2) you can basically forget about chr() giving consistent results.
How do you load the Excel sheet? If you export it to CSV, and then run SQL*Loader with the correct NLS_LANG setting it should basically just work.
The same if you use an ODBC connection and have the right NLS_LANG in the registry.
Oh, and while we are at it: AL32UTF8 support has gone through many changes in versions, patch sets and patches, so post your EXACT Oracle and OS version also.
Also:
- What data is seen in the excel column
- What data do you see in the Oracle column when you just select it
- What's the reason you think you have to get rid of that two bytes.
[Updated on: Wed, 09 March 2011 17:09] Report message to a moderator |
|
|
|
| Re: Unable to remove hidden character from field [message #498604 is a reply to message #498381] |
Thu, 10 March 2011 08:04 |
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
|
Senior Member |
|
|
Just for furture visitor whoever faces the same problem:
i don't think there will be soloution for this after you load the data in the database. I am using perl program which read out the data from excel and loads into flat file. This flat file eventually use for oracle feed. I basically modify my perl program which search for these hidden character and replaces them before it load into flat file.
Thanks
Sid
[Updated on: Thu, 10 March 2011 08:05] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
