White spaces in select 1

[lory]

Is there some way to make SQL Server not append white spaces to fields when the data is returned from a select, but still return any white spaces placed in the field by a user?

 

[SQLSister]

What data type is the field?

Questions about posting. See faq183-874

 

[SQLBill]

What do you mean by white spaces being returned?

CHAR will ALWAYS return extra spaces even if the user didn't put blanks in because CHAR is a 'set in stone' length. (CHAR(5) is always a length of 5).

If you are talking about the results in Query Analyzer, that is set by the column length in Tools>Options. It's not really how much is returned, it's how wide the column shown is set for.

-SQLBill

 

[lory]

We are selecting data from SQL, then using the results to perform sqr processing. The sqr program cannot deal with all the extra spaces at the end of the data and therefore we are not able to run the program effectively.

I realize why the spaces are there, but would really like to find a way to select and only return the data. I thought I would ask to see if it is even possible?

 

[nigelrivett]

A select will only return the data in the field. It won't pad unless you tell it to.
check what is in the table - if that's not what is returned then it is probably your client that is causing problems.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dbomrrsm]

Give this a try - I know it removes excessive spaces betweeen words i.e. Date_____Unknown would become Date_Unknown but not sure if it would work on trailing and leading spaces. Also I have only tested this on Varchar.

DECLARE @2spc char(4)
SET @2spc='%'+SPACE(2)+'%'
WHILE EXISTS (SELECT * FROM your_table_name
WHERE CHARINDEX(SPACE(2),RTRIM(column_name))<>0)
BEGIN
UPDATE your_table_name
SET column_name=
SUBSTRING(column_name,1,CHARINDEX(SPACE(2),column_name)) +
LTRIM(SUBSTRING(column_name,CHARINDEX(SPACE(2),column_name),LEN(column_name)-CHARINDEX(SPACE(2),column_name)+1))
WHERE RTRIM(column_name) LIKE @2spc
AND SUBSTRING(column_name,CHARINDEX(SPACE(2),column_name)-1,1)<>SPACE(1)
END

DBomrrsm

 

[ESquared]

lory, SQLSister's question needs to be answered before we can help you: what data type are the fields? Varchar or Char or what?

 

[lory]

CHAR

 

[ESquared]

That's it then... Char *always* pads fields with extra spaces up to the length of the field. Talk to whoever you need to talk to about converting it to Varchar. Or, use the equivalent of Trim/LTrim/RTrim in your data retrieval program.

 

[lory]

Thank you ESquared, I will do as you suggested.

 

[SQLBill]

Or if you can't change the column(s) to VARCHAR, you can use CAST or CONVERT in your query to change the data to VARCHAR.

-SQLBill

Posting advice: FAQ481-4875 and FAQ222-2244

 

[ESquared]

What SQLBill said!