How To Concatenate Strings That Have Trailing Spaces?

Jul 20, 2005

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

View 4 Replies


ADVERTISEMENT

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

Trailing Spaces

Apr 28, 2003

If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!

Todd

View 2 Replies View Related

Trailing Spaces - Such A Pain...

Dec 31, 2006

Hi All...  I'm using a SQL Server 2005 database.  I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out.  I guess I can understand why...  But it's a pain dealing with it.   As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet.  What's the easiest/best way to get rid of those spaces.  Geez, it'd be real cool if I could put something in the SELECT statement.  Any thoughts?  Thanks much!!  -- Curt
 

View 1 Replies View Related

Trailing Spaces Added To All Fields

Mar 15, 2006

For some reason, there are extra trailing spaces being added to all my
data as it is placed in the db.  I am collecting information from,
processing a TRIM-like function in javascript, and then again in the
ASP.net code before it is being placed in the db. 

The extra spaces are causing problems with my application.

Any help would be greatly appreciated.

View 3 Replies View Related

Move Trailing Spaces To Front

Aug 29, 2001

I have a column that is varchar(12) that the data was entered left justified
such as '12345 ' with trailing spaces. I need to move the number to be right justified to link with another table so it looks like ' 12345'. I looked at the right command and could not find a solution. An ideas?
Thanks

View 1 Replies View Related

How To Get Values From Column With Trailing Spaces

Jul 23, 2013

Performing security audit using command to retrieve data from Active Director about security groups and drop results into local tbl for analysis.

EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'

Problem is the col created to store result is varchar(1000) and can hold 1-3 values (loginIDs) per row with lots of trailing/white space.

E.g. (EmpID101, EmpID250 EmpID10)

Is there a technique to extract the needed value (loginIDs) from col?

View 4 Replies View Related

Export Data Without Trailing Spaces

Jul 23, 2005

Hello,when I export data from a table to a text file, I get trailing spacesif the data type in char. (This dosen't happen if the data type isvarchar). I can get rid of the spaces by using the trim() function onevery signle column. here is an example:DTSDestination("first_name") = DTSSource("last_name")My question is:Is there any easier way to get ride of the training spaces for allcolumns when exporing a table? It is too time consuming if I have totype trim() for every single column in the table.Thank you in advance,Eddy

View 1 Replies View Related

UPDATE - Removing Trailing Spaces

Jul 20, 2005

I have three columns, RecordID, FirstName, and LastName, but somehowthrough some program glitch, there is sometimes a trailing space inthe firstname and lastname columns, for example, a persons name couldbe entered as "John " "Smith" or "Bob " "Johnson "I know there is a RTRIM function in sql, but the problem I/m having ismaking an update line go through each row, and removing trailingspaces on those two columns. Any help will be greatly appreciated.Thanks in advance.

View 1 Replies View Related

Help For Trimming Leading And Trailing Spaces

Jan 7, 2008



Hi
Is there any way to trim all the leading and trailing spaces in all the column in a table.
JigJan

View 5 Replies View Related

Trim Trailing White Spaces

Nov 14, 2006

Hi All,

I have a column which has some white spaces that I suspect is tab delimeted one. So when I use a rtrim(col1) it would not trim those. So i used a scrip component and wrote this line,

Row.trimDetail = RTrim(Row.detail)

here trimdetail is an o/p column and detail is the input col with the trailing spaces.

but still I don know why the column has that spaces. Can someone help me to figure out what is the problem ?

Thanks in advance,

View 11 Replies View Related

Trailing Spaces In Nvarchar Columns

Nov 30, 2007



Hi All,

For €śnvarchar€? column if we pass the value as spaces, then it stores as space.

Eg:


declare @path nvarchar(50)

set @path = ' '

select '=>' + @path + '<='



Is there any setting / configuration that will force it to automatically trim the spaces and store it as null?

Thanks in Advance,
Palani

View 1 Replies View Related

Pad Trailing Spaces In A Report Expression

Sep 14, 2007

I am trying to pad a fixed number of trailing spaces into a report expression, as follows:

Data:

CUSTOMER_NAME-------------MichaelPeterJohn

Result (e.g. with 10 spaces padded, assuming all names are below 10 characters long):

"Michael ""Peter ""John "

Is there an easy way to achieve this ?

Thanks.

Kenny

View 1 Replies View Related

Values Entered In Db Has Trailing White Spaces

Apr 24, 2006

Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like    dropdownlist.items.findbyvalue(),  it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks

View 1 Replies View Related

T-SQL (SS2K8) :: RTRIM Not Removing Trailing Spaces?

Jul 14, 2014

I am loading a dimension using a distinct query.There are duplicates coming through and the only differnce is a trailing space on one of the columns.

RTRIM is not removing the space.

how i can fix it?

View 4 Replies View Related

Transact SQL :: Remove Trailing Spaces From A Column

Aug 31, 2015

The table I have is:

CREATE TABLE [dbo].[FTE2015](
[Firm Number] [varchar](50) NULL,
[w9] [varchar](50) NULL
) ON [PRIMARY]
GO

select * from dbo.FTE2015
Firm Number w9
709485""   0
040898A" 12.5
709502"" 2.4
041382"" 0.4
709503"" 0.3
709681""  4.9

How do I remove the trailing blanks? I tried RTRIM but it does not work.

SELECT RTRIM([Firm Number])
FROM dbo.FTE2015;
(No column name)
709485""
040898A"
709502""
041382""
709503""

How can I resolve this? The [Firm Number]column is not of a fixed length.

View 11 Replies View Related

Preserve Leading And Trailing White Spaces On Report

Dec 27, 2007

I spent huge amount of time figuring out how to preserve lading and trailing white spaces on report display without success. Can anyone help me here?

My problem is I have data with leading and or trailing white spaces and I need to show it as is. In designer preview it shows correct values. As soon as report is published and accessed on web, it truncates the whitespaces . I had a look at source, it shows values are correctly fetched(with spaces) but are ignored while rendering. I also tried replacing blank space with &nbsp;, however it reads this as &amp;nbsp;.

I am using asp.net 2.0 and SQL serer 2005 reporting services.

View 4 Replies View Related

Trailing Spaces On Output Parameter Of SQL Task - Different Behavior On Different Machines

Dec 20, 2007



Hello Everyone,

I've seen many entries about trailing spaces but have not found one like this.

In the Control Flow I am using an "Execute SQL Task" to populate some SSIS local variables (type string) by: (1) executing a SQL stored proc with output variables (type varchar(100)) to (2) be mapped to the local variable name (the parameter mapping Data Type is VARCHAR).

One of these mapped outputs is used as a path for subsequent operation in the Control Flow. At execution the sproc fires, populating the local variable with the path but with trailing spaces out to 255. Later in the "Script Task" when that path is used I receive an error telling me that the path is too long, and something about 260 or 246 characters.

Here's the oddity. I have two desktop environments running XP and a server environment (server 2003). This package runs just fine on the server - no trailing space issue, no need to trim. But on both my desktops I get the errors. By adding trim statements I can get back the correct path, but varchars should not be including trailing spaces, and the sproc return variable is a varchar (100).

I know this soulds like numerous other posts which indicate the solution is to trim, but I think the question I am asking is why does it work on the server but not the desktop? Is the SSIS variable type string experiencing a bug on different OS's?
Not to further complicate the issue but it used to work on my laptop, but through a horrible sequence of events I had to reload the studio in which case the error started to happen on that too.

View 6 Replies View Related

Keeping Trailing Spaces On Function Returning Nvarchar(4000)

Mar 21, 2008

I'm trying desparately to write a PadRight function in SQL Server 2005. I seem to be failing miserably because the trailing spaces disappear when the data is returned. First of all, why does SQL Server think I want my string trimmed? And second, how do I overcome this? Code below:




Code Snippet
CREATE FUNCTION [dbo].[PadRight]
(

@sourceString NVARCHAR(4000),
@length INT,
@padCharacter NCHAR(1) = ' ',
@trimBeforePadding BIT = 1
)
RETURNS NVARCHAR(4000) AS
BEGIN

DECLARE @returnStringLength AS INT, @toReturn AS NVARCHAR(4000)
SET @toReturn = LEFT(@sourceString, @length)


IF @trimBeforePadding = 1

SET @toReturn = RTRIM(LTRIM(@toReturn))
SET @returnStringLength = LEN(@toReturn)
IF @returnStringLength < @length

SET @toReturn = @toReturn + REPLICATE(@padCharacter, @length - @returnStringLength)
RETURN @toReturn
END
GO

View 8 Replies View Related

Integration Services :: Flat File Destination Columns Are Too Wide (too Many Trailing Spaces)

Nov 6, 2015

I'm loading data from a sql server table into a flat file. The flat file connection manager has the following settings

GENERAL:

Format:Delimited
Text Qualifier:"
Header row delimiter: {CR}{LF}
Header rows to skip : 0
Columns:
Row Delimiter: {CR}{LF}
Column delimiter: comma(,)

View 4 Replies View Related

Integration Services :: SSIS - Data Load To Excel - How To Retain Trailing Spaces In Text Column

May 8, 2015

I am loading data using SSIS 2008 from a table in SQL Server 2008 DB to excel 97 sheet pre-defined with column headers. All the columns in excel is has 'Text' format property and the columns in the SQL Server table are defined as nVarchar. One of the columns has trailing spaces in few rows in DB but after exporting to excel 97, the spaces are gone. We need to retain the whitespaces in the column values. How can we do that.

View 3 Replies View Related

Concatenate 2 Sql Strings

May 2, 2008

Hi,
Whats wrong with the following T-SQL?

DECLARE @SQL NCHAR(4000)

SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'

PRINT @SQL

I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?

View 8 Replies View Related

Recursively Concatenate Strings?

Nov 10, 2006

I have a table within a table that contains descriptions of work done as strings. Some of these need to be edited after a report is generated so it needs to be exported to Excel.

As everyone knows Excel can't process a table within a table so I need a way to concatenate all the separate row entries in the nested table to one Field entry so the nested table is no longer necessary.

Is there a way to recursively concatenate strings from the report designer?
The number of rows that need to be concatenated is variable so it has to be flexible.

View 2 Replies View Related

Aggregate/Concatenate Strings In A Script Component?

Sep 12, 2007

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f

I would like an output that looks like this:

CustNumb Invoice
1 a, b
2 c
3 d, e, f


I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...
Hilary

View 10 Replies View Related

Fuzzy Grouping Matching Nulls To Empty Strings/spaces

May 30, 2007

Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?



(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).



Thanks in advance.

Ben

View 3 Replies View Related

Cursed With Trailing Whitespace Or Trailing 0's

May 14, 2006

I have a databound textbox that is used to store a decimal value.

If my sql table stores this column as a decimal(2,2), then all of the numbers entered into the field will automatically put decimal places in that I don't want. For example, 45 becomes 45.00... 34.5 becomes 34.50.

If I set the sql table to nchar(10) and the dataset to system.string (max length of -1), then the number looks the way I would like it, however after a datatable update I end up with trailing whitespace after the number - filling up the rest of the unused 10 characters. For example, "45" becomes "45 " (8 spaces afterwards).

Does anybody know how I can fix this? I would prefer to store the numbers in SQL as a string (nchar(10))... but I don't know how to get rid of that darned whitespace. I would like to remove it at the database level and not at the client level if at all possible.

Thanks!

View 1 Replies View Related

Trailing 0's

Jul 20, 2005

I have a column defined as DECIMAL(10,6). I want to display it as astring but I do not want the trailing zeros. I cannot seem to get CASTor CONVERT or STR to exclude the zeros.Examples:45.340000 --> 45.3427.700000 --> 27.755.000000 --> 55Is there a function that will do this or do I need to write my own?

View 5 Replies View Related

Remove Trailing Zero

Jun 11, 2008

Would someone mind helping me with formatting a string please??
I have a column DECIMAL(4,2).  In a stored proc I am selecting this field and converting it to varchar so I can append certain characters to it (this is really irrelevant to my question).  However, before appending the characters, I need to remove zeros after the decimal point.
Examples:
3.00 should be 3
3.20 should be 3.2
3.05 should be 3.05
etc
 

View 6 Replies View Related

Trailing Zeros

Jun 27, 2006

Hi All,

Can anybody tell me how to remove the trailing zeros from the numeric field?

The data looks like this:

1.0000000
24.0000000
2356.0000000
61.0000000

It should look like this:

1
24
2356
61

Thanks.

View 3 Replies View Related

Delete Trailing ''

Aug 20, 2007



Hi,
I need to delete trailing slashes ('') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat, Rajneesh, Ankush, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

View 3 Replies View Related

How To Add Trailing Zeros 2 Decimals

May 18, 2008

In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.

Thanks for the info.

View 1 Replies View Related

How To Add Trailing Zeros If There Is No Decimals

May 19, 2008

How can i add two trailing decimals if there is no decimals:

for example if it is just 1, then make it 1.00

if it is 1.12 then leave it as it is.

can you please help.

select cast(ordernumber as varchar(10)) + '00' from ordertable

Thanks for the info.

View 1 Replies View Related

DT_Str And Trailing Whitespace?

Apr 26, 2007

I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).

I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.

Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?

Any help, of course, will be appreciated.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved