Comparing spaces characters

Question

Post reply

Comparing spaces characters

_ms65g_

SSCommitted

Points: 1504
More actions
May 19, 2010 at 1:26 am

#140298

Hi,
I would remove side-by-side duplicate charaters. I use following WHILE loop. but the CODE does not remove duplicate spaces. Why? how can I do that?
DECLARE @s-2 VARCHAR(MAX);
SET @s-2 = 'SSSSSSSQQQQQQLLLLL SSSSSeeeervvvvveerr'
DECLARE @r VARCHAR(MAX);
SET @r = '';
DECLARE @i INTEGER;
SET @i = 1;
WHILE @i <= LEN(@s)
BEGIN
IF @i = 1
SET @r = SUBSTRING(@s, 1, 1);
ELSE IF SUBSTRING(@s, @i, 1) <> SUBSTRING(@r, LEN(@r), 1)
SET @r = @r + SUBSTRING(@s, @i, 1);
SET @i = @i + 1;
END
SELECT @r AS [Result]
--See! But I need remove duplicate space characters during the function!
/*
Result
-------------------------
SQL Server
*/

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 1

I just ran your code, it is displaying "SQL Server"

what do you want in your output?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

You may probably wnat to read thro one excellent article from Jeff Moden on how to "REPLACE Multiple Spaces with One"

LINK : REPLACE Multiple Spaces with One[/url]

Hope this helps you!

_ms65g_ SSCommitted Points: 1504 More actions · Answer 3

ColdCoffee (5/19/2010)
You may probably wnat to read thro one excellent article from Jeff Moden on how to "REPLACE Multiple Spaces with One"
LINK : REPLACE Multiple Spaces with One[/url]
Hope this helps you!

Thanks for the link to the Article. I like it!

I just ran your code, it is displaying "SQL Server"
what do you want in your output?

Are you sure about that? There are not any multiple duplicate space character between 'SQL' and ‘Server’?

_ms65g_ SSCommitted Points: 1504 More actions · Answer 4

I fine the mistake!

Assume my string is: DECLARE @s-2 VARCHAR(500) = 'abc 'SELECT LEN(@s) --The result will be 3! not 5! This was my problem

Why SQL Server does not calculate spaces for end part of a string?!

WHILE @i <= LEN(@s)

BEGIN

IF @i = 1

SET @r = SUBSTRING(@s, 1, 1);

ELSE IF SUBSTRING(@s , @i, 1) <> SUBSTRING(@r, LEN(@r+'1')-1, 1)

SET @r = @r + SUBSTRING(@s, @i, 1);

SET @i = @i + 1;

END

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Use DATALENGHT function, This will calculate white spaces at both ends of the string. This might help your cause!

_ms65g_ SSCommitted Points: 1504 More actions · Answer 6

ColdCoffee (5/19/2010)
Use DATALENGTH function, This will calculate white spaces at both ends of the string. This might help your cause!

Thanks a lot ColdCoffee! 🙂

I have not used this function yet.