Removing Multiple Blank Space Characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Removing Multiple Blank Space Characters

Author

Topic

nns80
Starting Member

9 Posts

Posted - 2006-02-14 : 19:35:52

I have a select query on a table that returns a column which has multiple blank spaces in it. My questions is how can I replace multiple blank spaces with a single one.

Heres an example:

DECLARE @TableData TABLE (some_column VARCHAR(100))

INSERT INTO @TableData SELECT 'This_is__a_____test'
INSERT INTO @TableData SELECT 'This__is__a___test__2'

Please read the underscores as blank spaces.

SELECT some_column FROM @TableData

The desired output should be:

This is a test
This is a test 2

Thanks in advance.
-NS.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-14 : 20:09:49

Well it isn't pretty but it can be fast if you have some finite number of consecutive spaces:


SELECT	replace(
	replace(
	replace(
	replace(
	replace(some_column,'  ',' '),
	'  ', ' '),
	'  ', ' '),
	'  ', ' '),
	'  ', ' ')
FROM @TableData

Otherwise you'll need to parse, trim, then re-assemble the pieces which isn't too fast for a large set of data.
The only other way I can think of is to itterate through doing replaces until there are no more consecutive spaces. This will also have poor performance.

Be One with the Optimizer
TG

nns80
Starting Member

9 Posts

Posted - 2006-02-14 : 20:38:16

I dont know how many blank spaces will be there at any instance. So dont know to what extent should I go concatenating the Replace() function calls.

-NS.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-14 : 20:46:08

Well, if you only need to perform this operation on 1 (or a few) rows at a time, then it's probably cleaner to use a function that can handle an unlimitted number of spaces. If you need to apply this logic to 10s of thousands of rows at once and you think you'll have less than...say...40 consecutive spaces, I'd go with the nested replaces. If you need help with a function that can handle unlimited spaces let us know. (or look through the script library forum)

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 02:09:10

Other approach

Select replace(replace(replace(some_column,' ',' ^'),'^ ',''),'^','') from @TableData

Madhivanan

Failing to plan is Planning to fail

nns80
Starting Member

9 Posts

Posted - 2006-02-15 : 02:16:39

This did the trick. Seems to be working gr8. I just had to replace '^' with a unique string that won't occur in the column.

Thanks,
-NS.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-02-15 : 09:26:22

personally i enjoy 'þ', but arnold fribble bashed me about it