Stored Procedure + Remove multiple Spaces

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)

Stored Procedure + Remove multiple Spaces

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-13 : 16:06:45

Guys,

I have a table with two fields

Vendor varchar(20)
VendorResume text(16)

I want to remove/reduce/replace multiple spaces with a single space in the VendorResume field. Does anyone have a store procedure handy for this.

Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-10-13 : 18:53:58

You can create a function like this:


CREATE FUNCTION remove_spaces(@str varchar(8000))
RETURNS varchar(8000) 
AS
BEGIN 

WHILE CHARINDEX('  ', @str) > 0 
	SET @str = REPLACE(@str, '  ', ' ')

RETURN @str
END

Then:

SELECT Vendor, dbo.remove_spaces(VendorResume) FROM YourTable

But that won't work if your text column exceeds 8000 characters.

If you want to permanently remove the spaces use similar logic using an update statement.

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 08:27:15

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

Kristen

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-14 : 11:59:51

Thanks for the link Kristen,i have stored procedure shown below that looks for any weird characters one at a time and replaces with a single space...i thought using the same procedure with some modifications for replacing multiple spaces with a single space...but i am afraid that if i find 4 or 5 or more spaces and replace with a single space...not sure how the change in datalength effects the procedure?? can you suggest any modifications to the code...


CREATE PROCEDURE ResumeCleanup    
@Vendor varchar(20)

AS

declare @ptr binary(16) ,
    @i int ,
    @datalen int ,
    @DelPos int, 
        @Char int

select @i = 0
    , @datalen = datalength(VendorResume) 
from Vendor
where Vendor = @Vendor

while @i < @datalen
begin
    select @DelPos = @i
    select @i = @i + 1
    Select     @Char = ascii(substring(VendorResume,@i,1))
    from Vendor    
        where Vendor = @Vendor
if @Char < 32 or @Char = 255
        begin
select     @ptr = textptr(VendorResume) from Vendor           where Vendor = @Vendor
updatetext Vendor.VendorResume @ptr @DelPos 1 ' '
        end
end

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 13:27:22

I would resist doing is the way you have as its going to be very slow on the SQL Box, and not scale well.

I would either clean it up client side, or use a Tally table and PATINDEX to locate the "rogue" characters and replace them with spaces (i.e. Set Based rather than sequentially / loop based)

Kristen

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-14 : 14:00:46

Kristen,
any example or sample code for set based operation...or just the sample modified code version of the code stored procedure...

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 14:31:11

I haven't got one handy, otherwise I would have posted it

. and not got time at the moment to construct one, sorry. I'm sure one will exist though ... so some Googling ought to turn one up.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 02:29:53

>>any example or sample code for set based operation

Did you see Corey's method in this link?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 04:06:58

That's not going get rid of "any weird characters" though, is it Maddy?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 04:28:57

No It is not

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 04:37:30

Well go on then, you've got time to kill, knock a little sample up. Post it next to one of Igor's!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 04:39:01

But I am not Clever Programmer

Madhivanan

Failing to plan is Planning to fail

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 06:29:58

Hi Dupati!
Do you have some reason for replacing weird chars with blanks? Why not
replacing them with nothing?

Just a guess...

HCL

"If it works fine, then it doesn't have enough features"

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 07:12:26

"Do you have some reason for replacing weird chars with blanks"

If it was me I would want [using "XXX" to represent a Weird Character] to change:

"somethingXXXweird" to "something weird"

and "something XXX weird" to "something weird"

Kristen

HCLollo
Starting Member

49 Posts

Posted - 2005-10-17 : 07:26:32

Yes, but then you could use


REPLACE(REPLACE(string,weird_char+' ',' '),weird_char,'')

though I'm still with handling this in the front-end, should dupati
use one...

HCL

"If it works fine, then it doesn't have enough features"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources