Post reply

White space

  • July 19, 2013 at 7:43 pm

    #395172

    How do I edit the white space out of a table or created an edited table I imported from Excel?

  • July 20, 2013 at 6:05 am

    #1634250

    That depends on what you want to do. To remove all leading and trailing spaces, you would do:

    UPDATE tbl

    SET col = rtrim(ltrim(col))

    WHERE col <> rtrim(ltrim(col))

    To collapse multiple sequences of spaces, you could do:

    UPDATE tbl

    SET col = replace(col, ' ', ' ')

    WHERE col LIKE '% %'

    The later UPDATE does not handle tabs, which you need to handle separately.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 20, 2013 at 2:04 pm

    #1634267

    Thanks, for you time answering my question.

    After creating a csv file, importing it into a Excel spreadsheet and finally importing it into a SQL table I notices white space both left and right of the text. My goal was to edit this white space out.

    Part of my reason starting this project was to learn SQL.

  • July 20, 2013 at 2:06 pm

    #1634268

    mikej 14403 (2013-07-20)

    After creating a csv file, importing it into a Excel spreadsheet and finally importing it into a SQL table I notices white space both left and right of the text. My goal was to edit this white space out.

    The first query should take care of that.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 20, 2013 at 4:35 pm

    #1634276

    mikej 14403 (7/20/2013)

    Thanks, for you time answering my question.

    After creating a csv file, importing it into a Excel spreadsheet and finally importing it into a SQL table I notices white space both left and right of the text. My goal was to edit this white space out.

    Part of my reason starting this project was to learn SQL.

    Why not import the CSV file directly instead of going through Excel?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 20, 2013 at 6:09 pm

    #1634284

    I imported the csv file into Excel to edit it. I wanted to clean up the data before I created a table.

    As I have stated. Part of my goal as been to use this to use SQL. I'm sure there are ways to edit a table using SQL.

  • July 20, 2013 at 9:47 pm

    #1634288

    Understood. So what kind of a cleanup did you need to do? In the vein of teaching someone a little about SQL, I will typically run CSV files into a "staging" table to validate and "cleanse" the data instead of using a spreadsheet. Don't get me wrong... SQL will never be a substitute for what the human brain can do but you can make it do an awful lot in the area of validating and cleansing of data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2013 at 3:11 am

    #1634297

    mikej 14403 (2013-07-20)

    I'm sure there are ways to edit a table using SQL.

    There is. Did you try the UPDATE statement, I gave you?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 21, 2013 at 2:22 pm

    #1634324

    I tried the first set of code.

    UPDATE [dbo].[1870_1880_DAT]

    SET [TOWN] = rtrim(ltrim([TOWN]))

    WHERE [TOWN] <> rtrim(ltrim([TOWN]))

    The query ran with out errors.

    I tested the query's output by using a SELECT DISTINCT query. The output wasn't ordered sequentially in ascending order. I have only see this kind of output because I had a space in front of the text in the field of a record.

  • July 21, 2013 at 2:27 pm

    #1634325

    mikej 14403 (7/21/2013)[hrI tested the query's output by using a SELECT DISTINCT query. The output wasn't ordered sequentially in ascending order.

    To get an ordered output, you need to supply an ORDER BY clause. Without it, SQL Server is free to return data in any order.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 21, 2013 at 7:02 pm

    #1634334

    I heard TSQL defaulted in SELECT DISTINCT queries to sorting by ASC. The ORDER BY worked.

    My next problem will be to order by the number of entries in columns.

    I'm enjoying learning SQL.

  • July 22, 2013 at 1:28 am

    #1634359

    mikej 14403 (7/21/2013)

    I heard TSQL defaulted in SELECT DISTINCT queries to sorting by ASC.

    Whenever you hear such things turn a deaf ear to it. The only way to get a guaranteed order from a SELECT statement is to add an ORDER BY.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply