none
Remove Spaces in columns in sql table

    Question

  • Hi,
         I have problem after exporting excel data to sql server. I got whitespaces in columns and I need to remove whitespaces in all columns in a single query. I can remove the whitespaces single column at a time.But I need to remove whitespaces in all columns at a time.Is there any possiblity to remove whitespaces in all columns at a time? Please provide solution.
    Monday, June 08, 2009 6:30 AM

Answers

  • Hi,
         I have problem after exporting excel data to sql server. I got whitespaces in columns and I need to remove whitespaces in all columns in a single query. I can remove the whitespaces single column at a time.But I need to remove whitespaces in all columns at a time.Is there any possiblity to remove whitespaces in all columns at a time? Please provide solution.

    select 'Update YourTableName  Set '+name+'=ltrim(rtrim(name))'  from sys.columns where object_id=object_id('YourTableName')
    and system_type_id in 
    (select system_type_id From sys.types where name like '%char%')
    Run this script it will create update script for your Varchar/nvarchar/char/nchar type columns. Chnage the table name and and other datatype if needed.

    Note : If it is in production, please not that this query is not having WHERE condition which will impact all the rows. Try this on a test db/table first and then run in production


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by SaranRam Thursday, June 18, 2009 10:03 AM
    Monday, June 08, 2009 7:25 AM
    Moderator

All replies

  • before insert data please set ansi_padding off


    family as water
    Monday, June 08, 2009 6:49 AM
  • Hi,

    You  can use following query for removing white spaces from all the columns

    UPDATE

     

    Table_name

    SET

     

    columnname= REPLACE(columnname,' ',''),

          columnname1

    = REPLACE(columnname1,' ','')

    and so on for as many columns as you have.



    Monday, June 08, 2009 7:22 AM
  • Hi,
         I have problem after exporting excel data to sql server. I got whitespaces in columns and I need to remove whitespaces in all columns in a single query. I can remove the whitespaces single column at a time.But I need to remove whitespaces in all columns at a time.Is there any possiblity to remove whitespaces in all columns at a time? Please provide solution.

    select 'Update YourTableName  Set '+name+'=ltrim(rtrim(name))'  from sys.columns where object_id=object_id('YourTableName')
    and system_type_id in 
    (select system_type_id From sys.types where name like '%char%')
    Run this script it will create update script for your Varchar/nvarchar/char/nchar type columns. Chnage the table name and and other datatype if needed.

    Note : If it is in production, please not that this query is not having WHERE condition which will impact all the rows. Try this on a test db/table first and then run in production


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by SaranRam Thursday, June 18, 2009 10:03 AM
    Monday, June 08, 2009 7:25 AM
    Moderator
  • Hi Madhu,
                 This is very useful to remove spaces in my column names.I forgot to mention that i need to remove white spaces in my column content and I need to remove all the spaces in column content at a time. 
    Ex:
            Employee_Name
           ----------------------
            Ram       
               Ram


    Tuesday, June 09, 2009 5:33 AM
  • you can do this


    Code Snippet
    CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
        BEGIN
    
        RETURN LTRIM(RTRIM(@string))
        END
    GO
    
     
    
    SELECT dbo.TRIM(‘     aaaaa strig    ’)
    AND After YOU can do this on update

    UPDATE
    
     
    
    Table_name
    
    SET
    
     
    
    columnname= dbo.TRIM(columnname))






    or use cte


    WITH trimmed AS 
    
    (
    
    SELECT LTRIM(RTRIM(fld1)) as fld1, LTRIM(RTRIM(fld2)) as fld2 ....
    
    )
    
    SELECT...
    
     
    
    --etc. 
    
    • Edited by gpspocket Tuesday, June 09, 2009 7:41 AM
    Tuesday, June 09, 2009 7:36 AM
  • Hey Its Very Late To reply but i thought this will help atleast others to resolve such tasks.

    Your problem:

    > After importing records from excel sheet you are getting column names as shown below

    example:

    Col1   Col 2  Col 3  <==as we can see the whitespace between col 2 and col 3

    ------  ------ -----

    row1

    row2

    so on...

    Now to overcome this problem first Import the records in ##Temp_Table

    SELECT *  INTO ##Temp_Table

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;

     

    Now

     

    into your Own_Table Without Whitespaces in column Name as Below:

    SELECT

    Column1 = Col1,Column2="Col 2",Cloumn3 = "Col 3"

    INTO Own_table FROM ##Temp_Table

     

    Thats It!!!

    Problem Solved... :-) Keep Rocking!!

     

    Database=C:\IMPORTS.xls','SELECT * FROM [sheet1$a3:n]') <=== here in [sheet1$a3:n] ,"a3" is to import the record from 3rd row and "n" is the columns "a" to "n'.



    Monday, March 28, 2011 9:52 AM
  • This is my two cents.

    SELECT  

    'EXEC sp_rename '''+t.NAME+'.'+c.Name+''', '+Quotename(Replace (c.name,' ',''),'''')

    FROM

    sys.tables t

    INNER JOIN sys.columns c ON t.object_id=c.object_id

    Where t.name like '%%'--filter by table if you like.

    Friday, September 23, 2011 3:42 PM