Returning text fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning text fields

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-22 : 08:12:53

I am using C++, ADO for a front end to my SQL Server DB and when I obtain a recordset from a table, the strings always include "filler spaces" eg : "String "

I get the recordset using stored procedures, is there a way to eliminate the following space characters from the text strings returned?

I believe that most of them are of type varchar.

Mike B

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-22 : 08:58:09

You should check that to be sure, the only time a varchar will preserve trailing spaces is if ANSI_PADDING is turned on. You can test this in Query Analyzer by running:

SELECT col1+'///' FROM myTable

If you see space(s) between the text and the ///, then ANSI_PADDING was turned on when the row was inserted. If you want to be sure that all trailing spaces are removed, you can use RTRIM() in the SELECT clause, or run an UPDATE and set the column(s) equal to RTRIM(column).

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-22 : 21:09:26

I am now sure, how do I turn off ANSI_PADDING?

Mike B

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-22 : 22:22:35

SET ANSI_PADDING OFF

But read up on it in BOL first.

Sarah Berger MCSD

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-23 : 11:28:43

quote:
Originally posted by robvolk

You should check that to be sure, the only time a varchar will preserve trailing spaces is if ANSI_PADDING is turned on. You can test this in Query Analyzer by running:

SELECT col1+'///' FROM myTable

If you see space(s) between the text and the ///, then ANSI_PADDING was turned on when the row was inserted. If you want to be sure that all trailing spaces are removed, you can use RTRIM() in the SELECT clause, or run an UPDATE and set the column(s) equal to RTRIM(column).

Well I read that turning off the ANSI_PADDING is not recommended and being a beginner with SQL Server, I think it is better to follow "recommended" procedures for now anyway. The RTRIM(colomn_name) works fine enough for now. Thank you!

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources