Consecutive Blank Spaces in Data

Question

Post reply

Consecutive Blank Spaces in Data

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
July 20, 2011 at 2:47 pm

#390021

Hello Everyone
Still dealing with HL7 garbage Data.
Is there a way to replace multiple consecutive blank spaces in data? Not at the beginning or end by using RTRIM or LTRIM, but in the middle of a string such as:
Hello, This is the type of string that I am talking about . As you can see, there are many consecutive blank spaces in this string
Thanks in advance
Andrew SQLDBA

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

I don't have a solid fast solution, but this would get the job done, however un-performant like.

Select 'start'

while @@rowcount <> 0

begin

update table set column = replace(column, 'double space', 'single space') where column like '%double space%'

end

SELECT 'end'

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

It is not that there are only double spaces, there are many multiple spaces. I guess that I could run that query over and over, eventually it will all run true by removing only two consecutive blank spaces.

Since the row count is very small, it is not so bad.

Thanks

Andrew SQLDBA

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

Ninja's, what's the benefit of the WHILE there? I would have skipped that, but I'm probably missing something.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

I just noticed that the sample data that I posted had all the consecutive blanks removed. So my sample did not look correct.

LOL

Andrew SQLDBA

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

mtillman-921105 (7/20/2011)
Ninja's, what's the benefit of the WHILE there? I would have skipped that, but I'm probably missing something.

This will replace all double spaces with a single space. So a triple space then becomes a double space. You need to run the update untill you have no rows left that get updated... which is why you also need to have the where condition in there to close the loop at some point.

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

AndrewSQLDBA (7/20/2011)
I just noticed that the sample data that I posted had all the consecutive blanks removed. So my sample did not look correct.
LOL
Andrew SQLDBA

The joys of html!

Don't worry, I understood what you meant.

As I said, there are better ways to code this performance wise, but for a 1 off, small amount of data my solution will work fine.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 7

Ninja's_RGR'us (7/20/2011)
mtillman-921105 (7/20/2011)
Ninja's, what's the benefit of the WHILE there? I would have skipped that, but I'm probably missing something.
This will replace all double spaces with a single space. So a triple space then becomes a double space. You need to run the update untill you have no rows left that get updated... which is why you also need to have the where condition in there to close the loop at some point.

Ah, so it would operate on the same row more than once then (and as many times as necessary). Impressive. 🙂

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

sturner SSC-Insane Points: 22411 More actions · Answer 8

set @URL = REPLACE(REPLACE(REPLACE(@URL,' ',' '+char(7)), char(7)+' ',''),char(7),'')

Here is a trick I use to turn all multiple spaces (2 or more..) into one space. Its handy for cleaning up URLS. Does not need a while loop.

The probability of survival is inversely proportional to the angle of arrival.

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

mtillman-921105 (7/20/2011)
Ninja's_RGR'us (7/20/2011)
mtillman-921105 (7/20/2011)
Ninja's, what's the benefit of the WHILE there? I would have skipped that, but I'm probably missing something.
This will replace all double spaces with a single space. So a triple space then becomes a double space. You need to run the update untill you have no rows left that get updated... which is why you also need to have the where condition in there to close the loop at some point.
Ah, so it would operate on the same row more than once then (and as many times as necessary). Impressive. 🙂

I wouldn't impressive, more quick and dirty. There are better ways to code this performance wise but assuming this is a one off cleanse of data you can make due with this even if this is slower.

I don't have the "proper" way coded here and no time to do it so I went with the easy solution ;-).

Lowell SSC Guru Points: 323518 More actions · Answer 10

from a post by Jeff Moden, where he showed the sneaky way to get rid of multiple repeating chars in a single pass;

this is replacing pound signs, but it should be obvious that any char could be stripped of repeats:

DECLARE @DupeChar CHAR(1)

SET @DupeChar ='#'

;With MySampleData

AS

(

SELECT '####A##B#####C#####' As TheString UNION ALL

SELECT '####A##B#######C###' As TheString UNION ALL

SELECT '###########################################################A##B#####C###' As TheString UNION ALL

SELECT '######A##B#######C#####' As TheString UNION ALL

SELECT '##This######has#multiple###unknown#################spaces#in########it.###' UNION ALL

SELECT 'So#####################does######################this!' UNION ALL

SELECT 'As################################does########################this' UNION ALL

SELECT 'This,#that,#and#the#other##thing.' UNION ALL

SELECT 'This#needs#no#repair.'

)

--===== Reduce each group of multiple spaces to a single space

-- for a whole table without functions, loops, or other

-- forms of slow RBAR. In the following example, CHAR(7)

-- is the "unlikely" character that "X" was used for in

-- the explanation.

SELECT REPLACE(

REPLACE(

LTRIM(RTRIM(TheString))

,@DupeChar + @DupeChar,@DupeChar + '~') --Changes 2 spaces to the OX model

,'~' + @DupeChar,'') --Changes the XO model to nothing

,'~','') AS CleanString --Changes the remaining X's to nothing

FROM MySampleData

WHERE CHARINDEX(@DupeChar + @DupeChar,TheString) > 0

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!

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

sturner (7/20/2011)
set @URL = REPLACE(REPLACE(REPLACE(@URL,' ',' '+char(7)), char(7)+' ',''),char(7),'')
Here is a trick I use to turn all multiple spaces (2 or more..) into one space. Its handy for cleaning up URLS. Does not need a while loop.

SWEEEEEEEEET! :w00t:

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 12

Thanks Everyone

Finally this junk is beginning to look like usable data.

Andrew SQLDBA

sturner SSC-Insane Points: 22411 More actions · Answer 13

Ninja's_RGR'us (7/20/2011)
sturner (7/20/2011)
set @URL = REPLACE(REPLACE(REPLACE(@URL,' ',' '+char(7)), char(7)+' ',''),char(7),'')
Here is a trick I use to turn all multiple spaces (2 or more..) into one space. Its handy for cleaning up URLS. Does not need a while loop.
SWEEEEEEEEET! :w00t:

LOL - I just noticed that my example was rendered as if run through a "space trimmer" also! The first literal should be two spaces, not one. the next two are one space each. Read between the lines and understand what is happening so you can add the extra spaces when you implement it.

The probability of survival is inversely proportional to the angle of arrival.