Post reply

TRIMing the entire table in one shot

  • October 17, 2005 at 6:41 pm

    #90399

    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

  • October 17, 2005 at 9:06 pm

    #597817

    you can do them all in one shot by removing the where login=@manualindex. Other than that it's hard to shorten the code.

  • October 18, 2005 at 3:11 am

    #597853

    can you give the exact SQL statement to achieve after removing 'where login=@manualindex' from the above statement.

  • October 18, 2005 at 5:49 am

    #597886

    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

  • October 18, 2005 at 6:47 pm

    #598114

    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.

  • October 19, 2005 at 6:37 am

    #598196

    I already answered that. It can't be done correctly.

  • October 19, 2005 at 9:29 am

    #598324

    This might be a case in which it makes sense to use dynamic SQL.

  • October 19, 2005 at 9:36 am

    #598329

    Why????? You got a magic way to call a function without printing its name??

  • October 19, 2005 at 9:56 am

    #598342

    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.

  • October 19, 2005 at 10:00 am

    #598346

    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.

  • October 19, 2005 at 11:17 am

    #598401

    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

  • October 19, 2005 at 11:23 am

    #598405

    its on the Resources menu at the top of the form

    or go straight there http://qa.sqlservercentral.com/scripts/

  • October 19, 2005 at 11:23 am

    #598404

    Blue menu on the top - Ressource - Script

  • October 19, 2005 at 6:20 pm

    #598543

    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

     

  • October 19, 2005 at 6:42 pm

    #598547

    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