SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 User-Defined string Functions Transact-SQL
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

X002548
Not Just a Number

15586 Posts

Posted - 06/15/2005 :  12:22:43  Show Profile  Reply with Quote
quote:
Originally posted by Igor2004

Dear Brett,

Why you are so inattentive ?



It's one of my best features...but I guess you'd have to make the call correctly though...I get 2.

select dbo.udf_Occurs( 'aau aau ', 'aau ')




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/15/2005 :  12:25:36  Show Profile  Reply with Quote
quote:
Originally posted by Igor2004

Bien sur ! Je les ai trouve.



Je ne suis pas aussi sûr vous des mercis de have..but du jeu. Johnny, lui indiquent ce qu'il a un ans!



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  18:07:18  Show Profile  Visit Igor2004's Homepage  Reply with Quote
Dear Brett,

select dbo.udf_Occurs( 'aau aau ', 'aau ')
I`m sorry, for roundings the result is 2
But it is essences of affairs does not change !
select dbo.udf_Occurs( 'au au au au au au ', 'au ')
-- display 8 (The wrong answer) but right answer is 6
Unless you do not see an error?
"Je ne suis pas aussi sûr vous des mercis de have..but du jeu. Johnny, lui indiquent ce qu'il a un ans"
I have not understood, write english .
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/16/2005 :  21:42:49  Show Profile  Visit Igor2004's Homepage  Reply with Quote
I do not see more any comments or any offers of optimization.

to X002548 Function udf_Occurs is erroneous, you agree with it ?

to robvolk and Merkin
I consider that time has come to publish my functions as article in www.sqlteam.com .
That you think in this occasion ?
to Merkin
I repeat the question
Let me now please can I post your rhyme in page
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
For instance,
"there once was a clever string function
that came to be hyperlinked with gumption
it was linked too often
by a self proclaimed boffin
who's translation was not fit for consumption"
Damian, Sydney, Australia"

Today I has received the letter
--------------------------------------------------------------
Thanks for the UDF’s. Great work.
Arthur Hill, CPA, MCSD
Programmer
--------------------------------------------------------------
Dear Arthur Hill,

Thanks for your opinion.
Let me now please can I post your opinion in page
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
BTW where you have seen my functions ?
With best personal regards, Igor Nikiforov
--------------------------------------------------------------
You may post my opinions where you wish. I found your UDF’s via a Google search at
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8618&
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  08:33:59  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
I'm very clever too

how about this for an occurs function (with overlaps)

It doesn't even have a while loop


Create function dbo.occurs
(
	@iStr varchar(4000),
	@fStr varchar(100)
)
Returns smallint As
Begin
	Declare @rStr varchar(100)
	Select @rStr = case when charindex(left(@fStr,1),@fStr,2)>0 then stuff(@fStr,charindex(left(@fStr,1),@fStr,2),0,'~') else Replicate('~',datalength(@fStr)+1) end

	Return(datalength(Replace(Replace(@iStr,@fStr,@rStr),@fStr,@rStr)) - datalength(@iStr))
End
Go

Select 'ABCABCABCABCA', 'ABCABCA', dbo.occurs('ABCABCABCABCA','ABCABCA') Union All
Select 'ABCABCABCCABCA', 'ABCA', dbo.occurs('ABCABCABCCABCA','ABCA') Union All
Select 'ABCABCABCABCA', 'ABCAB', dbo.occurs('ABCABCABCABCA','ABCAB') Union All
Select 'AAAAAAA', 'AA', dbo.occurs('AAAAAAA','AA') Union All
Select 'BLAH BLAH BLAH', 'A', dbo.occurs('BLAH BLAH BLAH','A')
Go
Drop function dbo.occurs
Go



Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Edited by - Seventhnight on 06/17/2005 08:35:32
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/17/2005 :  09:24:33  Show Profile  Reply with Quote
quote:
Originally posted by Seventhnight

I'm very clever too

how about this for an occurs function (with overlaps)

It doesn't even have a while loop



Damn, that was clever !!!

PS.
I am sure this can be fixed:
Select '~', '~', dbo.occurs('~','~')

rockmoose
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/17/2005 :  09:51:17  Show Profile  Visit Igor2004's Homepage  Reply with Quote
Dear Seventhnight

Your algorithm is very beautiful and clear, but unfortunately

declare @v1 varchar(400), @v2 varchar(40)
select @v1 = 'MXJHHHJR', @v2 = 'HHJR'
select dbo.occurs (@v1, @v2) -- display 2 (The wrong answer) but right answer is 1
select @v1 = 'WWWWWWWWWWW', @v2 = 'WWWWW'
select dbo.occurs (@v1, @v2 -- display 4 (The wrong answer) but right answer is 7


Elimination of errors is 95-99 % of work

I am now occupied by that that I specify errors to clever developers in their functions
Last line 'Drop function dbo.occurs' especially is pleasant to me :-) , your function it deserves .

Correct algorithm is
CREATE function OCCURS5 (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
declare @start_location smallint, @occurs smallint
select @start_location = charindex(@cSearchExpression, @cExpressionSearched, 1), @occurs = 0

while @start_location > 0
select @occurs = @occurs + 1, @start_location = charindex(@cSearchExpression, @cExpressionSearched, @start_location+1)

return @occurs
end
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  10:17:25  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
basically just change the '~' to some very non normal character...


Create function dbo.occurs
(
	@iStr varchar(4000),
	@fStr varchar(4000)
)
Returns smallint As
Begin
	Declare @rStr varchar(100)
	Select @rStr = case when charindex(left(@fStr,1),@fStr,2)>0 then stuff(@fStr,charindex(left(@fStr,1),@fStr,2),0,'ø') else Replicate('ø',datalength(@fStr)+1) end

	Return(datalength(Replace(Replace(@iStr,@fStr,@rStr),@fStr,@rStr)) - datalength(@iStr))
End


Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  10:51:06  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
while i agree that the 'correct' algorith is a while loop that counts through, I don't think that makes you clever at all. I would think that is the most obvious answer.

What is interesting is the thought processes that go into each solution. While the solution is definitely not 100%, it is very interesting. I had already found this error, and I don't see a practical purpose for such functionality.

Without the practical need for the obscure 2% functionality, why bother with the extra effort?

Anyhoo... i would say that you are about an average developer who has a very clever opinion of your work.

very clever developers come up with interesting solutions, not obvious ones

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Edited by - Seventhnight on 06/17/2005 10:52:47
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/17/2005 :  11:53:02  Show Profile  Reply with Quote
Funny...I'd be suprised at who would look for a string that has a trailing space...

And I have to look at why it's giving 8...hold on...

So now it's official, huh....I guess I should surrender....

Wile E. Coyote

http://www.pioneernet.net/curtis/wile_e/



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - X002548 on 06/17/2005 11:53:27
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/17/2005 :  11:54:27  Show Profile  Visit Igor2004's Homepage  Reply with Quote
to Seventhnight
Publish yours functions (and write "I guarantee that it works without errors")
We can long go on a circle - you write function, then there are errors, you write a new variant etc.
Then it is possible to continue discussion, show your work, instead of empty chatter!
If you will manage to find the best algorithm (without errors!) I shall be glad.
Probably you the ingenious developer but while except for a confused code with errors, you have shown nothing !
If definition " the ingenious developer " - that who writes a confused code
with errors, you are an ingenious developer !
If definition " the average developer " - that who writes a clear code
without errors, (also finds errors in a code of the ingenious developer) that I am an average developer.

Your ingenious code is not suitable for practical use,
" Ingenious developers ", publishing a code, one of the reasons of mistrust to my functions!

"very clever developers come up with interesting solutions, not obvious ones "
Certainly, I agree !
So, your function without errors, will convince me of that that you the best developer
I wish you success in a writing.
Regards, Igor Nikiforv.

I'm good programmer
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/17/2005 :  11:56:10  Show Profile  Reply with Quote
quote:
Originally posted by Seventhnight

i would say that you are about an average developer who has a very clever opinion of your work.

very clever developers come up with interesting solutions, not obvious ones



Now you've gone and upset Corey...have you no humanity?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/17/2005 :  12:13:39  Show Profile  Reply with Quote
OO...OO..I know, we should all use Cursros...Right Igor...or is that eye-gor?

Wait...what type of brain did you use to build those?

Abbey?

Abbey who?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/17/2005 :  12:24:26  Show Profile  Reply with Quote
Here...this fixes your anomalous data situation


USE Northwind
GO

CREATE FUNCTION udf_Occurs(@SearchString varchar(8000), @SearchArg varchar(50))
RETURNS int
AS
  BEGIN
	SELECT @SearchString=REPLACE(@SearchString,' ',CHAR(00)), @SearchArg=REPLACE(@SearchArg,' ',CHAR(00))
	RETURN (LEN(@SearchString)-LEN(REPLACE(@SearchString,@SearchArg,'')))/LEN(@SearchArg)
  END
GO

SELECT dbo.udf_Occurs('abcdefghijklmnopabc','abc')
GO

SELECT dbo.udf_Occurs( 'aau aau ', 'aau ')

SELECT dbo.udf_Occurs( 'au au au au au au ', 'au ')
GO

DROP FUNCTION udf_Occurs
GO



And I do Drops to clean up what I test...you must have an awful mess over there....scrub boy



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  13:09:48  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Dear Igor,

Why you are so inattentive? I called you an average developer (not a bad programmer). One of the major qualifications of clever is originality (atleast according to webster). There is nothing original or impressive about a function that is the most obvious method of approaching a given task. In other words, stating the obvious doesn't make you clever.

As for my code: I personally can not think of a practical application to count the occurances of 1 string in another and include overlaps. To me, it is simply a puzzle to play with. It results in me becoming more clever. I think we have proved over and over here at SQLTeam, that we can quickly and efficiently address practical real world tasks. We occasionally even tackle some silly non-practical tasks (like your occurs task).

I would be very interested to know if you have some practical reason for including the overlaps, as a real world example eludes me.

Oh, and since you are so very clever, you should be answering some of the many questions on the board, instead of patting yourself on the back for stating the obvious.

To anyone else: I still find my approach interesting, does anyone have any suggestions on how to readapt to make it more complete?

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  13:13:03  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Oh... and the irony of the this whole topic, is that in a query of anysize, I refuse to use a udf because it can severely affect run time of said query. The nice thing about may example is that it could be done inline and address practical needs.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 06/17/2005 :  13:41:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Then it is possible to continue discussion, show your work, instead of empty chatter!
The irony of this statement is so priceless I cannot stop laughing.

Corey, I bow before you in awe, you are truly a clever developer. Articulate too.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/17/2005 :  13:49:46  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
quote:
Originally posted by robvolk
...
Corey, I bow before you in awe, you are truly a clever developer.


you poking at me

quote:
Originally posted by robvolk
Articulate too.



i read alot

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/17/2005 :  14:18:41  Show Profile  Reply with Quote
I wonder when we'll be accused of being elitist again...

It's a wonderful day in the neighbor hood...

USE Northwind
GO
--[CTRL]+K
SELECT * FROM Products WHERE dbo.udf_Occurs(ProductName,'Ale') > 0



Can you say scan...say it with me sssscaaan



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/17/2005 :  18:27:07  Show Profile  Visit Igor2004's Homepage  Reply with Quote
Dear Corey,
"I called you an average developer (not a bad programmer)."
How you qualify yourselves?

"There is nothing original or impressive about a function that is the most obvious method of approaching a given task. In other words, stating the obvious doesn't make you clever."
All programming finally this moving 1 and 0.
I consider, that for the decision of a problem of search of ocurrences (include overlaps), the obvious algorithm most approaches.

"I would be very interested to know if you have some practical reason for including the overlaps, as a real world example eludes me."
-- Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character.
-- AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence]) Return Values smallint
I have the right to expect that always dbo.AT(@cSearchExpression, @cExpressionSearched , dbo.OCCURS(@cSearchExpression, @cExpressionSearched ) + 1) = 0
for instance

select @occurs = dbo.OCCURS(@cSearchExpression, @cExpressionSearched ), @i = 1
while @i <= @occurs
begin
select dbo.AT(@cSearchExpression, @cExpressionSearched , @i)
select @i = @i + 1
-- your code
end

Well, I take your a word, you can think up the best algorithm,
but now you do not have time, you are occupied by the decision of very important problems.
I am afraid, that your decisions of very important problems too contain errors,
if you solve them as write functions.

You have begun intellectual dispute as thought that can write original function, at you (while) nothing has turned out instead of showing force of your talent, you have written a lot of text.

My functions are not necessary to you, do not use them.
You do not see practical application, hundred thousand developers also will not see. But tens thousand developers, probably, given functions will be necessary.
Come in the morning http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 look downwards pages
Then come in the evening, look downwards pages
You will see that the quantity download grows.
I published here letters from developers to me.

But if once, or to your friends it will be necessary for you reliable functions (without errors), I ask you, do not write them (before you global problems stand!), and use my functions.
Sol, I shall use your original functions solving global problems when you learn to write function without errors !
The mankind has knowingly thought up a division of labour.

I solve average tasks, as I the average developer, you solve great tasks, as you the original developer !
Thanks that have paid attention to such silly task.
There can be each of us will be engaged in the business - you to greater, I to small.

to robvolk
As about that to publish wash average stereotyped functions in article (it is possible with your review) ?

With the best regards, Igor.

I'm good programmer
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000