Re: RTRIM function doesn't work

From: Steve Kass (skass_at_drew.edu)
Date: 08/04/04 

Date: Wed, 04 Aug 2004 17:52:08 -0400

It's the Unicode non-breaking space character. You should be able to
replace it, as suggested, with CHAR(32), the "regular" space.

Steve Kass
Drew University

Scott wrote:

>It is varchar. I think the character is not the space character, but something that looks the same.
>
>See Peter's responce.
>
>Thanks
>
>"Steve Kass" wrote:
>
>
>
>>Scott,
>>
>> RTRIM should remove the spaces, but in the UPDATE statement you assign
>>the trimmed value back to Data_Field_1. If the type of that column is
>>CHAR, not VARCHAR, the spaces will be put back. Could that what you're
>>seeing?
>>
>>Steve Kass
>>Drew University
>>
>>Scott wrote:
>>
>>
>>
>>>I have created a table used to validate data from many different sources before the data is processed. This table is used for many different processes, so the data domain in each column varies. I have a separate meta-data table that defines each column for each process. One of the pieces of information I am validating is an ID column which is an Integer. Before validation happens I perform some data cleansing, such as trimming all leading and trailing blank spaces in all the data columns.
>>>I have a stored procedure with this SQL statment in it:
>>>
>>>UPDATE tbl_Validate_Data
>>> Data_Field_1 = LTRIM(RTRIM(Data_Field_1))
>>>
>>>However, the trailing spaces are not removed from the column, which is causing another stored procedure to fail. I located the column that caused the error and it contains a space at the end of the number i.e. "1234567891 ". I even checked the ASCII charater to make sure it was a space at the end:
>>>
>>>select ascii(substring(data_field_1,11,1)) from tbl_Validate_Data
>>>where len(data_field_1) = 11
>>>
>>>which returned "160"
>>>
>>>Is there a reason the RTRIM does not remove the space?
>>>
>>>
>>>
>>>

Relevant Pages