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 VARCHAR(MAX);
SET @S = '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

Gopi Muluka

SSCarpal Tunnel

Points: 4594

May 19, 2010 at 1:56 am

#1169266

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

what do you want in your output?

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

ColdCoffee

SSC-Dedicated

Points: 39971

May 19, 2010 at 2:05 am

#1169270

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 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