DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to trim special character?

  1. #1
    Join Date
    Jun 2002
    Posts
    62

    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.
    Regards,
    Jack

  2. #2
    Join Date
    Nov 2002
    Posts
    39
    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".
    Sathish

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Code:
    UPDATE your_table SET your_col = SUBSTR(your_col,1,LENGTH(your_col)-1);
    Sanjay

  4. #4
    Join Date
    Jun 2002
    Posts
    62
    I used the method suggested by Sathish. It works for me very well. Thank you very much.
    Regards,
    Jack

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width