Removing blank space

Question

Removing blank space

Simon-220850

SSCrazy

Points: 2789
More actions
September 23, 2005 at 4:49 am

#89070

Hi
Is there a way through string manipulation to remove empty spaces from a field
E.g turn F R A N C E into FRANCE.
I thought I would try
UPDATE table SET country = REPLACE(country,' ','') FROM table WHERE <criteria>
However this does nothing as the '' is ignored

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check with the char function, maybe the space in that string is not the char(32) space. Other than that, your code should work.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 2

No I'm afraid that didn't work RGR, If I issue

REPLACE(address,' ','Z')

I get FZRZAZNZCZE, so It is recognizing the ' ' as a space in the first parameter

It would seem that for some reason that it doesn't like '', i.e. quotes with nothing between them, as a replacement character

Cheers

Padmaja Kundrapu Old Hand Points: 323 More actions · Answer 3

hi,

replace function works in your case. May be your criteria is not satisfying to get the records to be updated.

create table #temp

(cntry varchar(12))

insert into #temp

values ('F R A N C E')

select * from #temp

update #temp

set cntry = replace(cntry,' ','')

select * from #temp

Simon-220850 SSCrazy Points: 2789 More actions · Answer 4

Hmmm,

I'm wondering whether it's down to the fact that I'm using SQL 7.0 in 6.5 compatibility mode or mayber it's a database setting somewhere

Select replace(country,' ','') AS SPACEFIELD, replace(country,' ','Z') AS ZDIELD from table

yields

SPACEFIELD ZFIELD

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

F R A N C E FZRZAZNZCZE

...etc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

If the replace works as you seen to state, then I'd really recheck that where condition to be absolutely sure that it is doing what you think it should be doing. Also I'd check to see if you have any rollback anywhere that is not throwing any errors (maybe a trigger or something in QA)

Simon-220850 SSCrazy Points: 2789 More actions · Answer 6

Hi RGR,

Yes I've removed the Where criteria, I'll check some of the other items you mention

Cheers

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

If this is going into production, then I suggest you add a where condition like this :

where charindex (' ', columnName, 1) > 0 so that you update only the lines where something needs to be changed.

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

Simon - you could check (as remi said) to see if the space that you "see" is indeed one...

declare @Country varchar(15)
set @Country = 'F R A N C E'
print ascii(substring(@Country, 2, 1))
select @Country = replace(@Country, char(32),'')..explicitly specify char(32)
--select @Country = replace(@Country,' ','')
print @Country

**ASCII stupid question, get a stupid ANSI !!!**

Lowell SSC Guru Points: 323518 More actions · Answer 9

what happens to countries that have two part names, ie "United Kingdom", "United States", or "Federated States of Micronesia"?

woudn't you compact them and turn them to wrong values?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

Maybe these are represented as "England", "America", "Micronesia" etc...

The good thing about a list like this is that it shouldn't have more than 200 values (or so), so "eyeballing" it should still not be a problem...though with world peace being just a pipedream and countries dividing against themselves all the time...who knows how much that # will grow...

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

If you recieve your data with upper and lower case lettering, you can use the ASCII characters to keep the seperation. This would make for a complicated function.

Otherwise, I agree with sushila. 200 plus is an awfully small number to handle something once rather than spend a lot of time working up a function with all sorts of what-if's.

I wasn't born stupid - I had to study.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 12

Fortunately there are no 2 word countries in my data . As I mentioned though, the Replace function doesn't seem to have any trouble identifying the ' ' as a space as replace(country,' ','Z') does exactly what I'd expect, but maybe as is mentioned there maybe some other nasty characters lurking in there that are giving the peculiar results when I use replace(country,' ',''). I'll do a little more investigating testing against char(32)

Thanks All

Farrell Keough SSCoach Points: 17414 More actions · Answer 13

Do a search on this site as well. Remi has posted a function that will use a table with all sorts of possible odd characters for Replacement. We get that when some information is passed into ASP. Some of the stuff has very odd characters...

Good luck

I wasn't born stupid - I had to study.