Replace double+ blank by single blank

Question

Replace double+ blank by single blank

Barkingdog

SSCoach

Points: 18670
More actions
September 12, 2002 at 11:22 am

#101246

I need a quick t-sql script that replaces all occurrences of 2 blanks in a column with a single blank.
TIA,
Bill

Viewing 9 posts - 1 through 8 (of 8 total)

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

sjcsystems Ten Centuries Points: 1347 More actions · Answer 1

Use the REPLACE() function on the column name - arguments are: column name, string to search for, string to replace with):

declare @string varchar(10)

SET @string='a b c d'

SELECT REPLACE(@string,' ',' ')

returns 'a b c d'

Is that OK?

Regards

Simon

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

quote:
Use the REPLACE() function on the column name - arguments are: column name, string to search for, string to replace with):
declare @string varchar(10)
SET @string='a b c d'
SELECT REPLACE(@string,' ',' ')
returns 'a b c d'
Is that OK?
Regards
Simon

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

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

quote:
Use the REPLACE() function on the column name - arguments are: column name, string to search for, string to replace with):
declare @string varchar(10)
SET @string='a b c d'
SELECT REPLACE(@string,' ',' ')
returns 'a b c d'
Is that OK?
Regards
Simon

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

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Might look at either one of these if you want to remove all the white space between words down to a single space

http://www.geocities.com/sqlserverexamples/#string1

http://www.geocities.com/sqlserverexamples/#string2

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

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Barkingdog SSCoach Points: 18670 More actions · Answer 5

Thanks to everyone for the answer. Unfortunately, the last part of my message got cut off. Our table has 10+ million rows and one field in those rows may contain a double blank. About 5000 such records (my estimate) exist in the database. I don't want to run Replace blindly (over 10 million records) when only a handful are "offenders".

I need a smart or selective way to run Replace.

TIA,

Bill

quote:
Use the REPLACE() function on the column name - arguments are: column name, string to search for, string to replace with):
declare @string varchar(10)
SET @string='a b c d'
SELECT REPLACE(@string,' ',' ')
returns 'a b c d'
Is that OK?
Regards
Simon

sjcsystems Ten Centuries Points: 1347 More actions · Answer 6

I'm not sure about quick, but put this in your UPDATEs WHERE clause:

...AND CHARINDEX(' ',<<colname>>) > 0

Simon

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

---You might consider this two step approach

create table blank(

field_blank char(100))

insert into blank values('yyy yyyy')

insert into blank values('xxxx xxxx')

insert into blank values ('xxx xxx')

insert into blank values ('zzzzzz zzzzz')

-- Step 1: first run this and review results

select field_blank as original,

substring(field_blank,1,charindex(' ',field_blank)-1) + ' ' + ltrim(substring(field_blank,charindex(' ',field_blank) + 1,len(field_blank)))

from blank

where CHARINDEX (' ' , rtrim(field_blank)) > 1

-- Step 2: After review run this

update blank

set field_blank = substring(field_blank,1,charindex(' ',field_blank)-1) + ' ' + ltrim(substring(field_blank,charindex(' ',field_blank) + 1,len(field_blank)))

where CHARINDEX (' ' , rtrim(field_blank)) > 1

select * from blank

drop table blank

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

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Jorge Cordero Ten Centuries Points: 1144 More actions · Answer 8

update tablename

set

colname = replace( colname ,

' ', /*2 spaces*/

' ') /*1 space*/

where

colname like '% %' /*2 spaces*/

should be enough,

I'll first try a simple select to find out the efficiency of the where clause:

select

colname

from

tablename

where

colname like '% %' /*2 spaces*/

quote:
Thanks to everyone for the answer. Unfortunately, the last part of my message got cut off. Our table has 10+ million rows and one field in those rows may contain a double blank. About 5000 such records (my estimate) exist in the database. I don't want to run Replace blindly (over 10 million records) when only a handful are "offenders".
I need a smart or selective way to run Replace.
TIA,
Bill
quote:
Use the REPLACE() function on the column name - arguments are: column name, string to search for, string to replace with):
declare @string varchar(10)
SET @string='a b c d'
SELECT REPLACE(@string,' ',' ')
returns 'a b c d'
Is that OK?
Regards
Simon