Re: RTRIM function doesn't work

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Wed, 04 Aug 2004 12:40:46 -0400

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?
>
>