| Author |
 Topic  |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 06/15/2005 : 12:22:43
|
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
|
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/15/2005 : 12:25:36
|
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
|
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/15/2005 : 18:07:18
|
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 .
|
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/16/2005 : 21:42:49
|
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&’ |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 08:33:59
|
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 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 06/17/2005 : 09:24:33
|
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 |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/17/2005 : 09:51:17
|
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
|
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 10:17:25
|
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." |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 10:51:06
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/17/2005 : 11:54:27
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/17/2005 : 11:56:10
|
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
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/17/2005 : 12:13:39
|
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
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/17/2005 : 12:24:26
|
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
|
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 13:09:48
|
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." |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 13:13:03
|
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." |
|
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 06/17/2005 : 13:41:14
|
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.  |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/17/2005 : 13:49:46
|
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." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/17/2005 : 14:18:41
|
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
|
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/17/2005 : 18:27:07
|
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 |
|
|
|
Topic |
|
|
|
User-Defined string Functions Transact-SQL
