|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem removing trailing spacesI 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 trailing spaces in the
varchar column? UPDATE tblname SET col = RTRIM(col) Pls test above first, just in case... -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ <mattm***@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: Show quote > So you have changed the datatype in the table and want to get rid of the trailing 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/ > > > <mattm***@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. -- Show quote--Brian (Please reply to the newsgroups only.) <mattm***@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! > 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 <mattm***@bellsouth.net> wrote in message Show quote 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! >  |
|||||||||||||||||||||||
Other interesting topics