How to Concatenate Strings That Have Trailing Spaces?

Jay Chan

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

This is what I want, and this is also what I get when I run the stored
procedure using Query Analyzer:

Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00

This is what I get if I run the stored procedure using ISQL
(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):

Test1, 2,Test1.txt, 1.00, 1.00
Test22, 2,Test22.txt, ,
Test333, 2,Test333.txt, 30.00, 30.00

You can see that the result from ISQL has the following differences:
1. It puts a space in front of each row.
2. It appends enough spaces at the end of each line to make
the line length to be exactly 61 characters.
3. It gets rid of the trailing space from each column.
4. It leaves only one blank space if the column has nothing
but a serie of spaces.

The following is the stored procedure that I am testing:

create procedure MyTest
as

set nocount on

create table #Test
(
Field1 varchar(10) null,
Field2 varchar( 5) null,
Field3 varchar(20) null,
Field4 varchar(10) null,
Field5 varchar(10) null
)
insert into #Test values
( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )
insert into #Test values
( "Test22 ", " 2","Test22.txt ", " ", " " )
insert into #Test values
( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )

select Field1 + "," +
Field2 + "," +
Field3 + "," +
Field4 + "," +
Field5
from #Test

drop table #Test
go

Strangely, the differences #3 and #4 only show up when I use the
SELECT statement on a table. They don't show up when I use SELECT
statements to show constant text strings or string variables, like
this:

set nocount on
select "Test1 " + "," +
" 2" + "," +
"Test1.txt " + "," +
" 1.00" + "," +
" 1.00"
select "Test22 " + "," +
" 2" + "," +
"Test22.txt " + "," +
" " + "," +
" "
select "Test333 " + "," +
" 2" + "," +
"Test333.txt " + "," +
" 30.00" + "," +
" 30.00"

The result is like the following if I use constant text strings or
string variables:

Test1 , 2,Test1.txt , 1.00, 1.00

Test22 , 2,Test22.txt , ,

Test333 , 2,Test333.txt , 30.00, 30.00

I need to run it from ISQL because that is how I run _all_ my other
stored procedures. I don't want to do anything differently just
because I need to run this stored procedure.

Thanks in advance for any suggestion.

Jay Chan

Jul 20 '05 #1

Ian Stocks

"Jay Chan" <ja******@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

Check out the 'SET ANSI_PADDING' setting in BOL.

Ian.

Jul 20 '05 #2

Erland Sommarskog

Jay Chan (ja******@hotmail.com) writes:

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

This is because with ISQL you get a different setting for ANSI_PADDING.
This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.

Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

Jay Chan

> Check out the 'SET ANSI_PADDING' setting in BOL.

Thanks! This switch fixes the problem very nicely. Now, all the
columns indent correctly.

Jay Chan

Jul 20 '05 #4

Jay Chan

> This is because with ISQL you get a different setting for ANSI_PADDING.

This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.

Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.
Yes, you are right. I add the switch in my stored procedure right
before it starts creating temporary tables, and now it can indent the
column of info quite nicely. Thanks.
The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)

I believe this is correct according to the Help info on that switch. I
also create tables in Query Analyzer; this explains why I haven't come
across this problem until now.

Jay Chan

Jul 20 '05 #5