Replacing repeating blanks with a single blank

[leela mn]

hi all,

I have a scenario where in i need to replace repeating blanks with a single blank of a column in the database.

ex: ASD KL OP ---> ASD KL OP

any suggestions??

Regards,
Leela

[ck9663]

hi all,

I have a scenario where in i need to replace repeating blanks with a single blank of a column in the database.

ex: ASD KL OP ---> ASD KL OP

any suggestions??

Regards,
Leela

the challenge: you don't know at any point in time how many spaces are there in between. this is string cleaning (like address), you might want to create a function that parse the string and remove repeating blanks. consideration: not because you can not see does not mean it's blank. there are "unprintable" characters.

-- CK

[leela mn]

the challenge: you don't know at any point in time how many spaces are there in between. this is string cleaning (like address), you might want to create a function that parse the string and remove repeating blanks. consideration: not because you can not see does not mean it's blank. there are "unprintable" characters.

-- CK

but in my case i am sure that its a space but is there any one liner function which can do this trick?????

[ck9663]

but in my case i am sure that its a space but is there any one liner function which can do this trick?????

is this a one time project? how many fields are you trying to clean up?

-- CK

[leela mn]

is this a one time project? how many fields are you trying to clean up?

-- CK

its not a project or something, there is one column in the database which has spaces in between and needs to be removed.

[ck9663]

its not a project or something, there is one column in the database which has spaces in between and needs to be removed.

if it's a one-time, manual kind of task try:

update yourtable
set yourfield = replace(replace(replace(yourfield,'  ',' '),'  ',' '),'  ',' ')
where patindex('%  %',yourfield) > 0
 

you're going to have to run this a number of times, until it no longer returns any resultset. it will just replace any 2 consecutive space with 1 space. so, if there are 3 spaces in between string, it will be reduced to 2, but the second replace will reduce it further to 1. if there are 4 it will reduce to 2, and so on...

you can try this first:

select yourfield, replace(replace(replace(yourfield,'  ',' '),'  ',' '),'  ',' ')
from yourtable
where patindex('%  %',yourfield) > 0
 

this will give you an idea if you're in the right direction

-- CK

[leela mn]

thanks for ur info... :)
this i have tried i wanted some other better approach than this if any........

[ck9663]

create a string parser function...


-- CK

[hailua]

Create FUNCTION [dbo].[replace_blank]
( @input nvarchar(max))
RETURNS nvarchar(4000) As
Begin
Declare @output nvarchar(4000)
SET @input = ltrim(rtrim(@input))
SET @output = ''

While 1=1
Begin
IF PATINDEX('% %',@input) = 0 BREAK
SET @output = ltrim(rtrim(@output))+' '+ltrim(rtrim(SubString(@input,1,CharIndex(' ',@input))))
SET @input = SubString(@input,CharIndex(' ',@input)+1,Len(@input))
End
SET @output = Left(@output,Len(@output))+' '+@input
Return @output
End

-- select dbo.replace_blank('hello: is this the one you looking at?. ')

[hailua]

Or you can use
-----------------------------
Create FUNCTION [dbo].[replace_blank]
( @input nvarchar(4000))
RETURNS nvarchar(4000) As
Begin
While 1=1
Begin
IF PATINDEX('% %',@input) = 0 BREAK
SET @input = replace(@input,' ',' ')
End
SET @input = ltrim(rtrim(@input))
Return @input
End
-------------------------------
-- select dbo.replace_blank('hello is this the one you looking at. ')