how to trim special character?

I uploaded data using sql*loader. I think an inivisible special character is also imported. The length of the field in 1 more than the actual data. Can anyone tell me how I can trim the special character. TRIM function did not help.

Try this simple method:

First get the asci value of that character like the below ex:

SQL> SELECT DUMP(JOB) FROM EMP WHERE EMPNO = 7902;

DUMP(JOB)
--------------------------------------------------------------
Typ=1 Len=7: 65,78,65,76,89,83,84

In the above ex. the job column is having the value ANALYST.
If you wanted to remove the T character which is in the last
then

SQL> SELECT REPLACE(JOB,CHR(84)) FROM EMP WHERE EMPNO=7902;

REPLACE(J
---------
ANALYS

But make sure that the character you remove is not present in any other place. i.e
if the job is having value as ANALTYST then it will remove both the "T".

Code:

---

UPDATE your_table SET your_col = SUBSTR(your_col,1,LENGTH(your_col)-1);

---

Sanjay

I used the method suggested by Sathish. It works for me very well. Thank you very much.