formating data in a table - SQL Server Forums

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

formating data in a table

Author

Topic

abuhassan

105 Posts

Posted - 2006-12-04 : 08:50:53

Hi

I wanted to knoe if it is possible to remove the leading and trailing spaces by using the RTrim and LTrim?

Currently I have the table Below is it possible to format all the data i.e. remove all leading and trailing spaces in the table from every record?

select *

from
(
select 'Rivet' as [Name], '01' as PartNo, '1999' as Year union all
select 'panel', '041', '1999' union all
select '   side panel', '021', '1999' union all
select 'thread  ', '042', '1999' 

) t

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-04 : 08:52:23

update tbl
set fld = ltrim(rtrim(fld))

maybe
update tbl
set fld = ltrim(rtrim(fld))
where fld like ' %'
or fld like '% '

==========================================
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.

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 09:04:19

Well, we should probably have a discusssion about datatypes


DECLARE @x varchar(8000), @y char(8000)
SELECT @x = 'abc', @y = 'abc'
SELECT '"'+@x+'"', '"'+@y+'"'

SELECT @x = LTRIM(RTRIM(@x)), @y = LTRIM(RTRIM(@y))
SELECT '"'+@x+'"', '"'+@y+'"'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

abuhassan

105 Posts

Posted - 2006-12-04 : 09:43:11

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 09:54:40

quote:
Originally posted by abuhassan

Thanks

Ummmm, you're welcome?

Did you run my code?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

abuhassan

105 Posts

Posted - 2006-12-05 : 11:35:43

I ran the folowing code it did the job

update tbl
set fld = ltrim(rtrim(fld))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources