MS-SQL: trim leading or trailing white space in table rows

I have a table with over 4000 rows of data, each with one leading white space.

Q. How can I trim the leading white space in the existing table or insert the data into a new table without the white space???

LVL 1
kvnsdrAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
SELECT LTrim(ColA) NewColAName, LTrim(ColB) NewColBName
INTO NewTableName
FROM TableA
0
 
ShogunWadeConnect With a Mentor Commented:
UPDATE MyTable
SET Field1=LTRIM(RTRIM(Field1),
 Field2=LTRIM(RTRIM(Field2)  ...etc...

0
 
ShogunWadeCommented:
well there you have it.   1 solution for each possibility ;)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
HilaireCommented:
Also note that you'll have to use varchar/nvarchar datatypes in the new tables,
as char/nchar will keep putting trailing spaces at the end of the strings.

if you use the
"select .... into newtable from oldtable"
syntax to build the new table, use explicit casting :

select cast(ltrim(rtrim(....)) as varchar(<size>) as <fieldname>, .....
into newtable
from oldtable

0
 
HilaireConnect With a Mentor Commented:
oops missing parenthesis

cast(ltrim(rtrim(....)) as varchar(<size>))
0
 
kvnsdrAuthor Commented:
I attempting to use the Update method, however I must have the syntax incorrect because (0 row(s) affected)....

UPDATE tableNew
SET col_IP = LTRIM(t1.col_IP)
, col_Dns = LTRIM(t1.col_Dns)
from tableOld t1
0
 
ShogunWadeCommented:
the update method should perform a direct update on the same table.

UPDATE tableOld
SET col_IP = LTRIM(tcol_IP)
, col_Dns = LTRIM(tcol_Dns)
0
 
kvnsdrAuthor Commented:
For future users, here are all the versions and ALL work eceptionaly well, thank you again.....

-- Remove (trim) both leading and trailing white space from data............

select ltrim(rtrim(col_1))col_1,
       ltrim(rtrim(col_2)) col_2
into tbl_New
FROM tbl_Old

-- or --

UPDATE tbl_OLD
SET col_1 = LTRIM(rtrim(col_1))
     , col_2 = LTRIM(rtrim(col_2))

-- or --

select cast(ltrim(rtrim(col_1)) as varchar(50)) as col_1,
         cast(ltrim(rtrim(col_2)) as varchar(100)) as col_2
into tbl_New
from tbl_OLD


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.