SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


White space


White space

Author
Message
mikej 14403
mikej 14403
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 28
How do I edit the white space out of a table or created an edited table I imported from Excel?
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
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
mikej 14403
mikej 14403
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86263 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mikej 14403
mikej 14403
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86263 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
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
mikej 14403
mikej 14403
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 872
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search