String Manipulation - 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)

String Manipulation

Author

Topic

Freddie
Starting Member

29 Posts

Posted - 2006-03-29 : 15:46:17

Hello:

Can someone help to remove the middle blanks from a text?

ABC COMP. OF RIVER INC.

Thx,

cb

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-29 : 15:48:33

Declare @s varchar(100)

Set @s = 'ABC COMP. OF RIVER INC.'

Select replace (@s,' ','')

Srinika

Freddie
Starting Member

29 Posts

Posted - 2006-03-30 : 09:12:55

Hi Srinika:

I don't want to replace all the spaces with '' I would like to remove the 10 spaces between COMP & RIVER....the replace function does not work on this problem.

ABC COMP RIVER INC

Regards,

cb

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-30 : 09:27:02

Hi Freddie,
By initial glance the # of spaces is not obvious.
<Its this browser which doesn't show the 10 spaces between COMP & RIVER>

Anyhow, Post some sample data and expected results (U may not doing this for just only one string ?)
Is it always 10 spaces ?
Is it always COMP & River
Is it multiple spaces to be replaced with a single space
or what ?

Srinika

Freddie
Starting Member

29 Posts

Posted - 2006-03-30 : 11:19:21

It multiple records with any amount of spaces at any place...it is not consistant. I want to replace the multiple spaces with a single space....not sure if it's doable.

Thanks,

cb

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-30 : 11:24:50

Try the following:

Declare @s varchar(100)

Set @s = 'ABC COMP               RIVER       INC.'

Select replace (@s,'  ','')


Results : 
--------------------------
ABC COMP RIVER INC.

quote:
Originally posted by Freddie

not sure if it's doable.

Me too until trying :

Srinika

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-03-30 : 12:10:24

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195

Freddie
Starting Member

29 Posts

Posted - 2006-03-30 : 13:12:51

Thanks Arnold...Srinika check it out

picked up this code from the site and works perfectly. It's instant.

-- convert tabs to spaces
UPDATE a_tmp SET Nm = REPLACE(Nm, ' ',' ')
WHERE CHARINDEX(' ',Nm) > 0

-- now do the work.
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE a_tmp
SET Nm = SUBSTRING(Nm, 1, CHARINDEX(' ', Nm, 1)-1) + ' ' + LTRIM(SUBSTRING(Nm,CHARINDEX(' ', Nm, 1), 8000))
WHERE CHARINDEX(' ', Nm, 1) > 0

END

cb

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-30 : 14:46:28

quote:
Originally posted by Arnold Fribble

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195

I like that 3 page long thread about whitespace!

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-31 : 00:41:03

quote:
Originally posted by Srinika

Try the following:

Declare @s varchar(100)

Set @s = 'ABC COMP RIVER INC.'

Select replace (@s,' ','')

Results :
--------------------------
ABC COMP RIVER INC.

quote:
Originally posted by Freddie

not sure if it's doable.

Me too until trying :

Srinika

Read the suggested link
Your solution may not work always


Declare @s varchar(100)

Set @s = 'ABC COMP                RIVER       INC.'

Select replace (@s,'  ','')

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-31 : 11:11:49

Thanks Madhi,

I read it, and found why mine doesn't work, all the time

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources