SQL to select and trim 1

[JezEling]

Hi All,

Can anyone tell me a SQL statement that will query a database and trim the trailing spaces from a field all at the same time? I know this is possible but I can't seem to figure it out.

Thanks in advance

Jez.

 

[Erics44]

you can use the rtrim functions

select rtrim([Field1]) as 'Field 1', rtrim([Field2]) as 'Field 2'
from Table1

or something like that

 

[gmmastros]

Be aware that there are several types of fields that can store strings. There is VarChar, nVarChar, Char, nChar, Text, and nText

The n in front of the data type signifies Unicode format, so non-english characters can be used (think Chinese/Greek/etc.)

Text and nText are the equivalent of Memo fields, allowing for billions of characters stored in a single field.

VarChar and nVarChar have a maximum of 8000 characters. This is preferred over text/ntext because there are many string manipulation fucntions that do not work with text/ntext.

Char and nChar are similar to VarChar and nVarchar. The main difference is that char/nchar will ALWAYS pad spaces to the end of a string.

To clarify my point, run the following in QA.

Select '->' + Convert(Char(20), 'Hello') + '<-'
Select '->' + Convert(VarChar(20), 'Hello') + '<-'

So, if your field(s) are declared an char or nchar, you may want to consider changing them to VarChar/nVarChar.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom