kvnsdr
asked on
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???
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???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
well there you have it. 1 solution for each possibility ;)
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
UPDATE tableNew
SET col_IP = LTRIM(t1.col_IP)
, col_Dns = LTRIM(t1.col_Dns)
from tableOld t1
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)
UPDATE tableOld
SET col_IP = LTRIM(tcol_IP)
, col_Dns = LTRIM(tcol_Dns)
ASKER
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
-- 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