Finding & removing a space from a string

Question

Post reply

Finding & removing a space from a string

Viewing 11 posts - 16 through 25 (of 25 total)

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

Well, in this case you WOULD need a loop 🙂

It seem like the extra whitespaces are being stripped out of the HTML. Is that a bug?

declare @String varchar(255)

--The string below has multiple whitespaces

set @String = 'Have a nice day'

While 1 = 1

begin

--this replaces all 2 concurrent whitespaces with 1

set @String = replace(@String, ' ', ' ')

--If no 2 whitespaces together, break

IF charindex(' ', @String) = 0 Break

end

select @String

PS: Nice webpage, Greg. I'll have to play around with that ROLLUP function; I've read about it but never really seen it in practical code.

Signature is NULL

guarddata SSCertifiable Points: 6583 More actions · Answer 2

BrenBart,

You are absolutely correct. When the problem has been to clean up formatting and reduce multiple occurences to single, I have also been resigned to while loops. Fortunately, the stated problem was to remove all spaces so the simple effort works here.

Guarddata-

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

There you go...use underscores instead of spaces.

declare @String varchar(255)

set @String = 'Have________a_____nice_____day'

While 1 = 1

begin

set @String = replace(@String, '__', '_')

IF charindex('__', @String) = 0 Break

end

select @String

Signature is NULL

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

A loop is not required to remove multiple white space. You can do it with just a number of replace statements like so. Actually I made this even more flexiable so the replacement value can be dynamic.

declare @String varchar(255)

declare @new_string varchar(255)

declare @cmd nvarchar(1000)

declare @replace_value varchar(10)

set @String = 'Have a nice day'

set @replace_value = ' '

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(@String,' +

''' '', ''' + @replace_value + '''),' +

''' '', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

set @replace_value = ''

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(@String,' +

''' '', ''' + @replace_value + '''),' +

''' '', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

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 5

Darn HTML, here it is with underscores:

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(@String,' +

'''_____'', ''' + @replace_value + '''),' +

'''____'', ''' + @replace_value + '''),' +

'''___'', ''' + @replace_value + '''),' +

'''__ '', ''' + @replace_value + '''),' +

'''_'', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

set @replace_value = ''

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(@String,' +

'''_____'', ''' + @replace_value + '''),' +

'''____'', ''' + @replace_value + '''),' +

'''___'', ''' + @replace_value + '''),' +

'''__ '', ''' + @replace_value + '''),' +

'''_'', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

Gregory Larsen, DBA

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

Gregory A. Larsen, MVP

guarddata SSCertifiable Points: 6583 More actions · Answer 6

Greg, For all practical purposes, that routine will work. However, it will not work in every case. Simple example: if you just used two replace commands (spaces and 1) and wanted to reduce to a single space. Starting with 3 spaces would produce two resulting spaces - not 1. Each successive replace can then be consumed by multiplying that 3. So if you start with 180 spaces, the result is two spaces, not one.

Yes it will work in most cases - but the only guaranteed way is to have a loop or you will get the occasional anomoly.

Guarddata-

BrenBart SSCrazy Points: 2622 More actions · Answer 7

Ok, last thing I'm going to say on this then I'll leave the dead horse for others to beat.

Sure, you can do it without a while loop but when did while loops join the dark side?

<Ow! Too many nested functions make my head hurt!>

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

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

The real solution is on my website. You just need two more replace statements like so:

declare @String varchar(255)

declare @new_string varchar(255)

declare @cmd nvarchar(1000)

declare @replace_value varchar(10)

set @String = 'Have__________________________________________________________________________________________________________________________________________________________________________________________________________________a nice day'

set @replace_value = '_'

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(replace(replace(@String,' +

'''_______'', ''' + @replace_value + '''),' +

'''______'', ''' + @replace_value + '''),' +

'''_____'', ''' + @replace_value + '''),' +

'''____'', ''' + @replace_value + '''),' +

'''___'', ''' + @replace_value + '''),' +

'''__ '', ''' + @replace_value + '''),' +

'''_'', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

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 9

The real solution is on my website. You just need two more replace statements like so to handle all cases. Also if you want an inline solution a loop will not work. Ok time to go home. see ya.

declare @String varchar(255)

declare @new_string varchar(255)

declare @cmd nvarchar(1000)

declare @replace_value varchar(10)

set @String = 'Have__________________________________________________________________________________________________________________________________________________________________________________________________________________a nice day'

set @replace_value = '_'

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(replace(replace(@String,' +

'''_______'', ''' + @replace_value + '''),' +

'''______'', ''' + @replace_value + '''),' +

'''_____'', ''' + @replace_value + '''),' +

'''____'', ''' + @replace_value + '''),' +

'''___'', ''' + @replace_value + '''),' +

'''__ '', ''' + @replace_value + '''),' +

'''_'', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

select @new_string

Gregory Larsen, DBA

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

Gregory A. Larsen, MVP

Calvin Lawson SSChampion Points: 11030 More actions · Answer 10

You'll have to admit that my While loop is more succinct, and the end result is the same.

I checked the execution costs, and they are identical (50/50)

snootchie bootchies.

Signature is NULL

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

I have to admit Calvin Lawson is correct the while loop is much easier to read and understandable, but I had to see for myself which performed better. So I did a quick test running each solution 100,000 times. From this test I found that the while loop was took around twice as long, as the multiple replace solution. Here is the code I used for my benchmark test.

set nocount on

declare @st datetime

declare @i int

declare @String varchar(255)

declare @new_string varchar(255)

declare @cmd nvarchar(1000)

declare @replace_value varchar(10)

set @String = 'Have________a_____nice_____day'

set @st = getdate()

set @i = 0

while @i < 100000

begin

While 1 = 1

begin

set @String = replace(@String, '__', '_')

IF charindex('__', @String) = 0 Break

end

-- select @String

set @String = 'Have________a_____nice_____day'

set @i = @i + 1

end

select 'While loop', datediff(ms,@st,getdate())

set @String = 'Have________a_____nice_____day'

set @replace_value = '_'

set @st = getdate()

set @i = 0

while @i < 100000

begin

set @cmd = 'set @new_string = replace(replace(replace(replace(replace(replace(replace(@String,' +

'''_______'', ''' + @replace_value + '''),' +

'''______'', ''' + @replace_value + '''),' +

'''_____'', ''' + @replace_value + '''),' +

'''____'', ''' + @replace_value + '''),' +

'''___'', ''' + @replace_value + '''),' +

'''__'', ''' + @replace_value + '''),' +

'''_'', ''' + @replace_value + ''')'

exec sp_executesql @cmd,N'@new_string varchar(255) out, @string varchar(255)',@new_string out, @string=@string

--select @new_string

set @String = 'Have________a_____nice_____day'

set @i = @i + 1

end

select 'replace with sp_executesql', datediff(ms,@st,getdate())

Gregory Larsen, DBA

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

Gregory A. Larsen, MVP