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

Transact-SQL (2000)

Puzzled

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-10 : 09:18:31

This isn't critical it's more of an 'out of interest' -

I have written a function:-

CREATE FUNCTION dbo.fn_StripAll
	(@InString varchar(4000), 
	 @SearchChars varchar(4000) = ' ')
RETURNS varchar(4000) 
AS
BEGIN
	DECLARE @Char varchar(1)
	WHILE LEN(@SearchChars) > 0
		BEGIN
			SET @Char = LEFT(@SearchChars,1)
-- assumes that the characters in @SearchChars are unique
			SET @InString = REPLACE(@InString,@Char,'')
			SET @SearchChars = RIGHT(@SearchChars, LEN(@SearchChars)-1)
		END

	RETURN @InString
END
GO

This looks for all the characters in @SearchChars that are in @InString. So for example STRIPALL 'ABCDEABCDE','CD' would return 'ABEABE'.

HOWEVER - I'm not very happy with it as it doesn't 'feel' very efficient even just looking at it. My questions are therefore these -
1) Is there a set based way of acheiving this
2) If not, what can I do to make this more efficient.

I get the distinct feeling that there is a lot that can be done with this but I'm not sure what though there is at least one clue to what I think in the code

For what it's worth I'm not interested in Unicode for this

A sarcasm detector, what a great idea.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-10 : 10:10:37

I think that the set-based solution for this would not be worth it in a function as you are dealing with exactly 1 value and a single list of characters to remove. It is efficient as a function to run once. The efficiency is questionable if you plan to run the function thousands of times. At that point, a set-based solution (outside of a function) would be significantly more worthwhile.

thats all i got

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-10 : 10:19:31

Sorry Corey I should have explained better.

It's a function that would be run monthly on around 50000 records which is why I started wondering about a set based solution. Perhaps it would be better as a stored procedure. I'm open to suggestions

thanks

steve

A sarcasm detector, what a great idea.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-10 : 11:09:45

here's a shot:


Declare @myTable Table (textVal varchar(100))
Insert Into @myTable
Select 'ABCDEFGABCDE' Union 
Select 'Bill Gates' Union 
Select 'Bubblicious Gum' Union 
Select 'Captian Crunch' Union 
Select 'Big Boy ''Bud''' Union 
Select 'That''s Enough'

Declare @remove varchar(10)
Set @remove = 'BD'

Select * From @myTable

while exists(Select * From @myTable Where patindex('%['+@remove+']%',textVal)>0)
Begin
	
	Update @myTable
	Set textVal = stuff(textVal,patindex('%['+@remove+']%',textVal),1,'')
	From @myTable
	Where patindex('%['+@remove+']%',textVal)>0
End

Select * From @myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources