Char Trim

Hi,

I imported records from text file using BCP, in one column I am seeing blank space in starting as well in end. I printed the ASCII code it turn out as 32. But when I tried to trim with LTRIM and RTRIM it didn't work. I tried to replace the character using REPLACE witn no avail.

Please help.

Thanks.

You could try the following:

RIGHT(LEFT(ColumnName, LEN(ColumnName) - 1), LEN(ColumnName) - 2)

This will trim the leading and trailing characters.

Is there a chance you could you check the results of the following just to confirm that the ASCII value of the character is 32 ? :

SELECT TOP 1 ASCII(LEFT(ColumnName, 1))
FROM MyTable

Thanks

Chris

|||

I confirmed that the char is 32. I am still seeing the blanks before and after the values.

|||Is the column CHAR or VARCHAR or NVARCHAR?|||

As Dan states, the datatype is interesting here, but if you are using rtrim and ltrim, it shouldn't matter... Please post some sample code like this so we can get an idea of what you are really wanting...

drop table test
go
create table test
(
charValue char(10),
varcharValue varchar(10)
)
go
insert into test
select 'Test' + char(32) ,
'Test' + char(32)
go
select '*' + charValue + '*' as char1,
'*' + ltrim(rtrim(charValue)) + '*' as char2,
'*' + varcharValue + '*' as varchar1,
'*' + ltrim(rtrim(varcharValue)) + '*' as varchar2
from test
go
char1 char2 varchar1 varchar2
*Test * *Test* *Test * *Test*

|||

Louis,

I was surprised to see that your varcharValue included the trailing space. I thought VARCHAR columns removed trailing spaces. So then I went to study the references and found that it depends on ANSI_PADDING in the database.

http://msdn2.microsoft.com/en-us/library/ms175055.aspx

The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.

Thanks for the education! ;-)

|||Thanks for the added info...|||

Hi,

Thaks for your help.

Column is Varchar(50) also I am seeing following property of column (please note I am using SQLServer 2005 SP1).

TrimTrailingBlank =0

FixedLenNullinSource=0

This column is contains database sizes like 2000 MB

I need to remove the MB thing which I am doing

Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize)))

then I have to convert to Numeric type but when I am trying

Cast(ltrim(rtrim(Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize))))) as Numeric(20,2))

getting error

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric.

Thanks

|||

I think the computation for the third parameter of SUBSTRING is not correct. I think you just want "(CharIndex('M',DatabaseSize)-1)".

declare @.DatabaseSize varchar(50)
set @.DatabaseSize = ' 2000 MB '

print ' ' + cast(LEN(@.DatabaseSize) as varchar)
print ' ' + cast(CharIndex('M',@.DatabaseSize) as varchar)

print ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as Numeric(20,2))
print cast(len(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))) as varchar)
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as binary)

Dan

|||

Thanks a lot,

Reason I put Len() function cause we do have some rows where MB was missing so what I did I concanated 'MB' and use ChartIdex -1 and it is working now !!!! All along I was looking at wrong place to resolve the problem.

Cheers.