sql server2000 looking for an "alltrim" function 3

[tempo1]

Hi everyone,
My table contains a 300 width column but i dont want to show that width in my query so i'm looking for
a function to "cut" empty spaces from that column. For example, if my column width was of 10 characters
and contained something like "a", i'd like it to be "a" and not " a".
so i write

 select alltrim(mycol) from mytable

apparently there is no "alltrim" function in SQL syntax so i'm looking for something that does the same.
Can anyone show me how ?
Thanks

 

[bborissov]

SELECT LTRIM(RTRIM(mycol)) from mytable

But do yourself a favor, define all these columns as varchar() and always update them w/o leading spaces.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tempo1]

Thanks a lot Borislav,
Did you mean that when creating a table i should define each character column as "varchar()" rather than
"varchar(#)" ?

 

[bborissov]

No, I mean when you define the column you should define it as varchar(xxxx) instead of CHAR(xxxx). Than you don't have to use a function like RTRIM(), and if you always remove the leading chars before updating the column there is no need to use LTRIM() also

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Using varchar does not prevent storage of trailing spaces. The char data type of course DOES store padding spaces up to the full length.

create a test table with a varchar(30) column
insert some rows, 'abc' and 'abc '

select a len() and a datalength() on the rows.

See spaces are stored.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

Yes, but when you store in varchar you made a proper algorithm in your frontend, doesn't you? All depends of you how to store the data.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>Using varchar does not prevent storage of trailing spaces

it also depends on your ANSI_PADDING setting

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tempo1]

Thanks