removing spaces when ltrim/rtrim don't work

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

General SQL Server Forums

New to SQL Server Programming

removing spaces when ltrim/rtrim don't work

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-09-06 : 09:43:48

I currently have a field which appears to have lots of spaces behind it. The field is defined as ID Char(50),null. I have tried using ltrim/rtrim (i.e. rtrim(ltrim(ID)) as 'Swap_ID' but when saved to a file, i still get the full 50 characters. Is there another way which may work to trim these extra spaces?
I have provided a small sample of the values in the field ID below.
Thank you for your time in advance.

ID
--------
19907CO
11120C0
18861CO
99982CO
19987CO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 09:48:35

Data type CHAR is used for fixed length so it is always filled with spaces.
You can use data type VARCHAR for what you need.

Too old to Rock'n'Roll too young to die.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-09-06 : 10:16:27

I can not change the Char(50) to Varchar as you suggested. I currently receive a sql table from our IT department as such, thus I can not change it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 10:25:12

Excuse me but I am a little confused...

When you are talking about "the file" then what does it mean?

Importing from a file into a table with a column ID CHAR(50)?

Exporting from a table with a column ID CHAR(50) into a file?

What exactly are you doing?

Too old to Rock'n'Roll too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-06 : 10:34:27

I don't understand the exact problem.


DECLARE @foo CHAR(50) = 'Hi There'

-- These don't work
SELECT '[' + @foo + ']'
SELECT '[' + CAST(@foo AS VARCHAR(50)) + ']'

-- These do
SELECT '[' + LTRIM(RTRIM(@foo)) + ']'
SELECT '[' + LTRIM(RTRIM(CAST(@foo AS VARCHAR(50)))) + ']'

Here the datatype is CHAR(50) and the first two won't give you what you want (a trimmed version)

However the second pair will.

However, if you end up dropping the results back into a fixed width variable then you will get space padding.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-09-06 : 10:53:01

I am basically trying to eliminate the spaces from the ID field I am selecting from a sql table. The results are exported to excel.
Thus, far I have tried:

SELECT LTRIM(RTRIM(CAST(ID AS VARCHAR(50)))) as 'Swap_ID' from table1
select rtrim(ltrim(ID)) as 'Swap_ID' from table1

and I still get the same issue of the spaces still being at the end when exported to excel. The spaces are not eliminated. Perplexed.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-06 : 11:01:07

It's possible they aren't really spaces at all, but some other "invisible" character like char(9), char(10) or char(13). I even ran into a hard space (char(255)) once. Try doing a REPLACE on these and see if that changes anything.
e.g., REPLACE(ID,char(9),'')

Jim

Everyday I learn something that somebody else already knew

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-06 : 12:37:42

you can read out the character values using something like this


DECLARE @foo CHAR(50) = 'afakg;lajneogiaogm' + CHAR(100) + CHAR(100)+ CHAR(100) + CHAR(100) + CHAR(255)

SELECT
	  '[' + @foo + ']'
	, '[' + LTRIM(RTRIM(@foo)) + ']'

;WITH numbers ([n]) AS (
	SELECT ROW_NUMBER() OVER ( ORDER BY a.[n] )
	FROM
		           ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS a ([n])
		CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS b ([n])
	)
SELECT
	[n] AS [CharacterIndex]
	, SUBSTRING(@foo, [n], 1) AS [Character]
	, ASCII(SUBSTRING(@foo, [n], 1)) AS [ASCII Value]
FROM numbers
WHERE [n] <= LEN(@foo)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources