TRIMing the entire table in one shot
-
Hi Guys,
Is there any statement by which we can remove all the leading and trailing white spaces from all the fields of a particular table. I am using SQL Server 7.
Right now I am removing these spaces manually for every record(and hence coloumn) that I am inserting into my table as follow:
INSERT INTO login_info(login,password,email) SELECT LTRIM(RTRIM(login)),LTRIM(RTRIM(login)),LTRIM(RTRIM(email)) from Master where login=@manualIndex
Is there anyway by which I can elimiante the multiple LTRIM,RTIRM from my SQL statement.
Thanks
-
you can do them all in one shot by removing the where login=@manualindex. Other than that it's hard to shorten the code.
-
can you give the exact SQL statement to achieve after removing 'where login=@manualindex' from the above statement.
-
As RGR said, just remove the where clause statement from your original statement - that way it will execute on all the records in the table.
INSERT INTO login_info(login,password,email)
SELECT LTRIM(RTRIM(login)),LTRIM(RTRIM(login)),LTRIM(RTRIM(email)) from Master
-
well thank you guys for your replies. Actually the statement that you guys suggested:
INSERT INTO login_info(login,password,email)
SELECT LTRIM(RTRIM(login)),LTRIM(RTRIM(login)),LTRIM(RTRIM(email)) from Master
In the above statement, I have to write LTIRM,TRIM for every coloumn name. And this is what I would like to eliminate???
I hope this clears the questions more.
-
I already answered that. It can't be done correctly.
-
This might be a case in which it makes sense to use dynamic
SQL. -
Why????? You got a magic way to call a function without printing its name??
-
sounds like what you really want is a function to trim off the leading and trailing blanks. Sql doesn't come with one but you can make your own
create function dbo.MyTrim
(@TrimThis varchar (8000))
returns varchar(8000)
as
begin
return rtrim(ltrim(@trimthis))
end
then just call it as you would a normal function
update mytable
set col1 = mytrim(col1),
col2 = mytrim(col2)....
but you still have to list every column you want to trim in your update or insert statement. check out the script library there are some nice scripts for building this kind of statement.
-
As I said... this is actually slower to run because you run it in a function (however so slightly). And you still have to type it for every column... as far as cutting and pasting is conserned, no time is saved there either.
-
Thank you guys for the suggestion. I guess I will have to go the usual way.
BTW, how can I access the script Library. Just curious.
Thanks
-
its on the Resources menu at the top of the form
or go straight there http://www.sqlservercentral.com/scripts/
-
Blue menu on the top - Ressource - Script
-
Don't necessarily recommend it.. but here is how its done...
Replace your_table_name_here with table name. check the output... change type list if necessary...
When you are ready to run, uncomment the exec statement... I commented it out for safety sake.
=============================================
DECLARE AllColumsAllTables CURSOR
READ_ONLY
FOR
SELECT a.name AS Column_Name, b.name AS Table_Name
FROM dbo.syscolumns a
JOIN dbo.sysobjects b ON a.id = b.id
WHERE b.xtype = 'U'
and a.xtype in (175,167) -- types char and varchar
and b.name = 'your_table_name_here'
ORDER BY b.name, a.name
DECLARE @columnname varchar(40)
DECLARE @tablename varchar(40)
declare @strSql nvarchar(1000)
OPEN AllColumsAllTables
FETCH NEXT FROM AllColumsAllTables INTO @columnname, @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @strSql = 'Update ' + @tablename + ' Set ' + @columnname + ' = ltrim(rtrim('+@columnname+'))'
print @strSql
--exec sp_executesql @strSql
END
FETCH NEXT FROM AllColumsAllTables INTO @columnname, @tablename
END
CLOSE AllColumsAllTables
DEALLOCATE AllColumsAllTables
GO
-
wow looks like that might work. Thanks for the effort. How do you get the number 175,167 for char and varchar. What if I had 'number' as type. Where do you get these codes from
Thanks
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply