Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

White space Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 7:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:09 PM
Points: 16, Visits: 28
How do I edit the white space out of a table or created an edited table I imported from Excel?
Post #1475722
Posted Saturday, July 20, 2013 6:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475749
Posted Saturday, July 20, 2013 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:09 PM
Points: 16, Visits: 28
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.
Post #1475770
Posted Saturday, July 20, 2013 2:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475771
Posted Saturday, July 20, 2013 4:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475781
Posted Saturday, July 20, 2013 6:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:09 PM
Points: 16, Visits: 28
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.
Post #1475790
Posted Saturday, July 20, 2013 9:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475797
Posted Sunday, July 21, 2013 3:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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?



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475806
Posted Sunday, July 21, 2013 2:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:09 PM
Points: 16, Visits: 28
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.

Post #1475837
Posted Sunday, July 21, 2013 2:27 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475838
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse