How to remove uneven spaces - SQL Server Forums

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

SQL Server Development (2000)

How to remove uneven spaces

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2003-06-10 : 02:38:20

Hi There !

Can anyone help me?
I have a column lastname having data with uneven spaces inbetween text, but the spaces inbetween are equal. Now i want to remove these uneven spaces and make only single spaces, how can i acheive this ?

Example:
--------
Lastname
---------
SAM JACKSON MIKE (2 Spaces)
SAM JACKSON MIKE (3 Spaces)
SAM JACKSON MIKE (8 Spaces)
Likewise i have many data stored in one column with spaces, now i want to truncate them to single space.
please give me a query...

regards
Ragh

Ragh

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-10 : 03:31:14

You could write a few UPDATE statements that replace multiple spaces with a single space...or you could write a pretty script to do that

DECLARE @NoOfSpaces TINYINT

SET @NoOfSpaces = 10

WHILE @NoOfSpaces > 1
BEGIN
UPDATE TableName SET LastName = REPLACE(LastName, SPACE(@NoOfSpaces), '')
SET @NoOfSpaces = @NoOfSpaces - 1
END

I havent tested it, so better take a backup before you try this!

Owais

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-10 : 03:58:06

Another way is to use one UPDATE and nested REPLACEs (see this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21511 )

ragh
Starting Member

34 Posts

Posted - 2003-06-10 : 04:45:22

Thanks guys, it works i just changed, ok some minor change thats it
but how can i implement this for SELECTing the rows without UPDATING...?

DECLARE @NoOfSpaces TINYINT
SET @NoOfSpaces = 20
WHILE @NoOfSpaces >= 1
BEGIN
UPDATE Pantable SET LastName = REPLACE(LastName, SPACE(@NoOfSpaces), ' ')
SET @NoOfSpaces = @NoOfSpaces - 1
END

Ragh

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-10 : 05:08:00

Another way is to use no UPDATEs and nested REPLACEs

(see this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21511 )

ragh
Starting Member

34 Posts

Posted - 2003-06-10 : 05:32:42

Thanks Arnold

does this works out...

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lastname,
REPLICATE(' ',21),' '),
REPLICATE(' ', 6),' '),
REPLICATE(' ', 3),' '),
REPLICATE(' ', 2),' '),
REPLICATE(' ', 2),' ')
FROM Pantable

Ragh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources