trim in Oracle 8i

[sweetleaf]

Hi there,

I have 2 questions.

1) I know that in 8i there is a TRIM string function.
My company is using 8.0.5, i have been using this
RTRIM(LTRIM(string1)).. is this acceptible?

2) I have a stored procedure which simply inserts data into a table. When i check the table after running the procedure i notice that the code is inserting strange characters (squares), i suppose this could be a carraige return or a 'blank', though i'm not sure? how can i prevent my code from insert this junk into the database?

Any help would be greatly appreciated!

 

[Turkbear]

Where does the SP obtain the data?

 

[jee]

In answer to your first question - the way you are using rtrim/ltrim should work fine. If you upgrade to Oracle 8i (or 9i), the code should continue to work because these functions are still supported.

Second question - the best solution would be to find the source of the strange data and get rid of it there, but if this is not possible, what you can do is use the ASCII function to see what ASCII (or EBCDIC) character is being appended (I assume you mean the strange characters are appending at the end of the good data). Once you know the ASCII code, you can remove the chars before inserting using RTRIM. For example, if you determine that the trailing characters are ASCII 01 you could then do
rtrim (string1, CHR (01))
to get rid of the unwanted characters.

 

[Bimalaggarwal]

I would use the REPLACE function.
Example, for carriage returns (chr(10),

replace (text,chr(10),'') ......

It would then be possible to eliminate such characters from within the text as well, and not just from the beginning or the end.