Removing blank spaces in records.

Question

Post reply

Removing blank spaces in records.

Trystan Hughes

SSCrazy

Points: 2109
More actions
May 16, 2006 at 10:17 am

#113442

Hi all,
I have a problem where I have imported quite a lot of data from an Excel file, and some of this data contains fields that have a lot of blank spaces inbetween characters. For example:
"I live in the town which is near this town"
Is there a query I can use to get rid of this? I guess if I knew the exact amount of space I could do a REPLACE(), but I don't know the exact spaces between characters.
Hope you can help.
Thanks
Tryst

Viewing 7 posts - 1 through 6 (of 6 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

no need to

REPLACE(MyData,' ','') will replace/remove every space.

Ahmad Ghanem Old Hand Points: 351 More actions · Answer 2

If your data in a column in table try this :

update [Name of Table] set name [Name of Column] = REPLACE ( [Name of Column] ,' ','')

This will remove any spaces between words in all column (Named column above)

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Those examples will remove all spaces (obviously) - which might well be what you want (it's not clear to me).

The example below will remove duplicate spaces - just in case that was what you wanted (unlikely).

--data

declare @t table (v varchar(8000))

insert @t select 'I live in the town which is near this town'

--calculation

declare @UnusedChar char(1)

set @UnusedChar = '¬' --or whatever - use char(x) if necessary

update @t set v = replace(replace(replace(v, ' ', ' ' + @UnusedChar),

@UnusedChar + ' ', ''), @UnusedChar, '')

select * from @t

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 4

Hi all,

and thanks for the replies.

If I had gone with the first two replies, that would have made my example, which was...

"I live in the town which is near this town"

...in to...

"Iliveinthetownwhichisnearthistown"

...which isn't what I wanted.

RyanRandall, I will give your code a try.

Thanks.

Tryst

jessinthomas012 SSC Rookie Points: 46 More actions · Answer 5

Hi Tryst,
Please try this one..

update [Name of Table] set name [Name of Column] =REPLACE(REPLACE(REPLACE(assessors_parcel_number, ' ', '*^'), '^*', ''), '*^', ' ');

Thanks!

Jessin

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

jessinthomas012 - Thursday, June 14, 2018 1:53 AM
Hi Tryst,
Please try this one..
update [Name of Table] set name [Name of Column] =REPLACE(REPLACE(REPLACE(assessors_parcel_number, ' ', '*^'), '^*', ''), '*^', ' ');
Thanks!
Jessin

Thanks for stepping up to the plate with that post, Jessin. It brings up a common problem and a solution that I used to think that was good... so much so that I wrote an article about the method a while back. That's when I learned of a method from the discussion that followed that's nearly an order of magnitude faster proving that this is an incredible community. Here's the article along with the note at the beginning that takes you to the post in the discussion that changed it all. And, the discussion has some remarkable tests done by some remarkable people.
http://www.sqlservercentral.com/articles/T-SQL/68378/

--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)