trim spaces from char/varchar column

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

trim spaces from char/varchar column

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 09:59:33

I have a problem with the spaces in the columns,when they are extracted from the table and written on to flat file some have spaces and some dont like

49049|318| |12.3|R|
49050|318||12.3|R|

So i would like to trim the leading and trailing spaces from every char/varchar columns used

Any idea why this is in happening in the flat file

Any suggestions
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:01:47

SELECT LTRIM(RTRIM(Col1))

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:11:43

CHAR columns always appends trailing spaces.

DECLARE	@Sample TABLE
	(
		c1 VARCHAR(20),
		c2 CHAR(20)
	)

INSERT	@Sample
SELECT	'1', '1' UNION ALL
SELECT	' 2', ' 2' UNION ALL
SELECT	' 3  ', ' 3  '

SELECT	'_' + c1 + '_',
	'_' + c2 + '_'
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-10-01 : 11:34:41

Thanks a lot...it worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources