Problem removing trailing spaces

Hi,
I am very new to SQL and made a dumb mistake when setting up my
database. I have some small tables I used for drop-down-lists in a web
page I designed. I used a datatype of CHAR. Well, I should have used
VARCHAR as it is causing me problems now.
I have redesigned the site so the drop-down-lists are not pulled from
the SQL table. The problem is that every record created with one of
these dropdown values has trailing spaces. In other words, if the field
was CHAR with length 10 when we store Canada, it is really
Canada<space><space><space><space>.
I have found one way to fix this:
UPDATE mytable
SET country='Canada' WHERE country='Canada'
This has the effect of removing the spaces. I guess I could also use:
UPDATE mytable
SET country='Canada' WHERE country LIKE '%Canada%'
This is WAY too tedious to do for every value of every column I have
this problem with. Is there a way to iterate through the database a
remove ALL trailing spaces?
I appreciate the help!So you have changed the datatype in the table and want to get rid of the tra
iling spaces in the
varchar column?
UPDATE tblname
SET col = RTRIM(col)
Pls test above first, just in case...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||I read about RTRIM but didn't realize it could be used that way. I will
test it. Thanks!
Tibor Karaszi wrote:
> So you have changed the datatype in the table and want to get rid of the t
railing spaces in the
> varchar column?
> UPDATE tblname
> SET col = RTRIM(col)
> Pls test above first, just in case...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <mattmerc@.bellsouth.net> wrote in message
> news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...|||First, in Enterprise Manager you need to redefine the Char(10) column as
VarChar(30), becuase Char columns cannot be trimmed. The rtrim() function
will returns a string with trailing spaces trimmed. You will probably want
to trim every country in the table, so there is no need to specify a where
clause.
UPDATE mytable SET country = rtrim(country)
http://sqlcourse.com/create.html
http://sqlcourse.com/update.html
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||You can also use the RTRIM() function, but this still require you to update
every column. Although it might be easier to drop, recreate the tables
using varchar, and reload them.
--Brian
(Please reply to the newsgroups only.)
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>