| Author |
 Topic  |
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
 Posted - 06/13/2005 : 15:00:22
|
Hmm, irony or sarcasm. It's so hard to choose. This is like observing a Yank and a Brit watching Homer Simpson watch Monty Python to see what he finds funny and why.
Be One with the Optimizer
TG |
 |
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/13/2005 : 22:17:19
|
to rockmoose
"Soyez bienvenus" Merci
Vous parlez fran#231;ais, mes f#233;licitations !
Je pense que vous avez voulu dire "pour que ce soit la preuve de l'absence des erreurs"
Ó ìåíÿ ðóññêèé òåêñò îòëè÷íî îòîáðàæàåòñÿ.
This is my brief bio:
Igor Nikiforov is a professional programmer.
He has extensive experience in designing database applications using FoxPro,
VFP, C with LCK VFP and SQL Server.
He programs client/server applications using VFP and MS SQL Server.
He is a member of the Montreal Visual Studio User Group (www.ssmug.ca).
to elwoos
I`m sorry
declare @v1 varchar(20), @v2 varchar(1)
select @v1='d g t ', @v2 = ' '
select dbo.fn_CountChars(@v1, @v2), dbo.OCCURS(@v2, @v1)
-- display 0 (The wrong answer) and 3 (Right answer)
But it is essences of affairs does not change !
"As for the rest I never claimed it was pretty (or bug free)!"
Why you have published your functions ?
And why you have asked a rhetorical question ?
"I've written some string functions and got them published on the web. Does that mean I'm a clever developer?" |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/13/2005 : 23:16:48
|
Igor, you need to ask Graz for a blog so you can post more of your brilliant code.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/13/2005 : 23:18:12
|
| No no no, that would defeat his entire purpose. He's just supposed to LINK to his code, not actually post it. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/13/2005 : 23:19:56
|
I see. I believe his plan for world domination is in progress. hmmmmmmmmmmmm
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/13/2005 : 23:20:43
|
You laugh, but through such subtle methods does he divert all the world's web traffic to his site.
Ooooops, sorry Igor, didn't mean to give the whole plan away. |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/14/2005 : 01:03:19
|
to robvolk
The plan is rather simple to create the name having written the good functions benefitting.
To receive time to time such letters
-------------------------------------------
To Igor Nikiforov,
Thank you for sharing your code. I Googled 'SQL count occurrence of
character in a string' and bingo! Didn't have to write it and got so
much more.
Appreciate it.
Phil Youker
Programmer/Analyst
VCSSO/Information Technology Services
-------------------------------------------
to not hammer in forums any nonsense
to give efficient advice , for instance
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49733
Question: "is there a similar function in SQL server to ORACLES RPAD ?"
I do not speak English also I am don't the MVP, but the question is clear enough.
IMHO the answer is rather simple "Yes, see http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115"
But it appears it is possible to write about bcp etc .
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/14/2005 : 03:17:02
|
relax igor... they're just having fun at your expense. join in.. 
of course it's cool you shared the functions. keep up the good work.
and yes you are a clever developer.
Go with the flow & have fun! Else fight the flow  |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/14/2005 : 07:17:25
|
| And keep up the crossposting! 'Cause shit, we just don't get enough of that here already. |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/14/2005 : 12:36:04
|
OK, I'd like to see the function you wrote to find the occurances of a value in a string.
Post that here.
Alos, just as aside...let's assume that a udf is written as effeciently as possible.
How much faster are system function as compared to udfs?
How are system functions written btw?
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
|
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/14/2005 : 22:07:39
|
quote:
To splash by tongue everyone is able, to write something costing everyone isn't able.
Yoda would be proud.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/15/2005 : 02:27:06
|
to X002548
"OK, I'd like to see the function you wrote to find the occurances of a value in a string."
--------------------------------------------------------------------------------------
-- Returns the number of times a character expression occurs within another character expression (include overlaps).
CREATE function OCCURS (@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
--------------------------------------------------------------------------------------
"Alos, just as aside...let's assume that a udf is written as effeciently as possible.
How much faster are system function as compared to udfs?"
Function OCCURS to compare there is nothing, but idea good, having spent pair hours,
I for testing speed have written function similar built in function charindex
--------------------------------------------------------------------------------------
CREATE function CHARINDEX_FORTEST ( @cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @start_location smallint = 1 )
returns smallint
as
begin
declare @LenExpressionSearched smallint, @LenSearchExpression smallint, @starting_position smallint
select @LenExpressionSearched = datalength(@cExpressionSearched)/2, @LenSearchExpression = datalength(@cSearchExpression)/2, @starting_position = 0
if @LenExpressionSearched > 0 and @LenSearchExpression > 0
while @LenExpressionSearched - @start_location + 1 >= @LenSearchExpression
begin
if substring(@cExpressionSearched, @start_location , @LenSearchExpression) = @cSearchExpression
begin
select @starting_position = @start_location
break
end
else
select @start_location = @start_location + 1
end
return @starting_position
end
--------------------------------------------------------------------------------------
Here procedure for comparison of speed (is clear what to receive an exact times of work only the built in function in such a way it is impossible)
--------------------------------------------------------------------------------------
CREATE PROCEDURE COMPARETIME AS
declare @cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000),@start_location smallint, @i int , @cycle int, @s smallint, @bt1 datetime, @bt2 datetime
select @cSearchExpression = N'qwerty', @cExpressionSearched = N'tra lal lal la dfjkbndlfkb 1234123 dgbfknglbkjelkbtjeldgbfknglbkjelkbtjel dgbfknglbkjelkbtjel;bj 345789987 qwerty 2357634676', @start_location = 1, @i = 0, @cycle = 10000
select @bt1 = getdate()
while @i < @cycle
select @s = CHARINDEX(@cSearchExpression, @cExpressionSearched, @start_location), @i = @i +1
select @cycle, 'cycles charindex Milliseconds' , datediff(ms, @bt1, getdate())
select @bt2 = getdate()
select @i = 0
while @i < @cycle
select @s = dbo.CHARINDEX_FORTEST(@cSearchExpression, @cExpressionSearched, @start_location), @i = @i +1
select @cycle, 'cycles UDF CHARINDEX_FORTEST Milliseconds ', datediff(ms, @bt2, getdate())
GO
--------------------------------------------------------------------------------------
On small lines a difference of 10 percent.
In the example UD function works five times more slowly - on 10000 calls of 2.5 seconds.
Depending on length of a line function more slowly in some times, the more longly a line the more difference
But I in my functions for search use the built in function charindex !
"How are system functions written btw?"
I don't know, I can assume only that for function charindex the algorithm will resemble.
I do not think that my functions need to be caused on some millions times,
The quite good review here
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1093197,00.html
"Many of these functions would normally be performed by business logic (for instance, in an Active Server Page), but including them as user functions in SQL opens up a broad range of possible programming tricks."
"Keep in mind that user-defined functions in very large views or long-running stored procedures can significantly impact performance. The best time to use a function like that would be when you are generating results to be stored in a table."
I think that fears of delay of work are exaggerated.
|
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/15/2005 : 02:35:20
|
btw I have found a bug Transact-SQL
declare @cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000),@start_location smallint
select @cSearchExpression = N'qwerty'+nchar(0), @cExpressionSearched = N'qwerty', @start_location = 1
select CHARINDEX(@cSearchExpression, @cExpressionSearched, @start_location) -- display 1 error !
select dbo.CHARINDEX_FORTEST(@cSearchExpression, @cExpressionSearched, @start_location) -- display 0 Correct result
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 06/15/2005 : 02:38:57
|
quote:
Originally posted by derrickleggett
quote:
To splash by tongue everyone is able, to write something costing everyone isn't able.
Yoda would be proud.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
in essence object can nobody.  |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2039 Posts |
Posted - 06/15/2005 : 03:20:29
|
quote:
Why you have published your functions ?
Igor the reasons are two fold
Firstly so that more clever developers than I could provide constuctive criticism should they choose.
Secondly (and more importantly from my perspective) so that people who are even newer than I, who may need to do this sort of stuff have something to work from.
They are my version of the barebones of a word concept where a word is defined as all the characters between two delimiters. They were originally written some time ago and I have adapted them to use in SQL to provide the same functionality as the originals. This is why they aren't pretty or neccessarily bug free. If I was writing them from scratch now they may be prettier or more thouroughly tested (though I'm not sure my employers would be happy about the time it would take to do that)
These functions work for me for the very limited purpose they were written for (manipulating data into a format I need). I don't need them to be perfect as the input data for all of them in my application is a fairly controlled dataset. If I want them to work in more general cases in the future I will have to review them
steve
A sarcasm detector, what a great idea. |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/15/2005 : 10:09:37
|
to derrickleggett
I don't speak english, but Jack London speaks.
My favourite citation
"For the most part, the remaining four hunters leaned on the table or lay in their bunks and left the discussion to the two antagonists. But they were supremely interested, for every little while they ardently took sides, and sometimes all were talking at once, till their voices surged back and forth in waves of sound like mimic thunder-rolls in the confined space. Childish and immaterial as the topic was, the quality of their reasoning was still more childish and immaterial. In truth, there was very little reasoning or none at all. Their method was one of assertion, assumption, and denunciation. They proved that a seal pup could swim or not swim at birth by stating the proposition very bellicosely and then following it up with an attack on the opposing man's judgment, common sense, nationality, or past history. Rebuttal was precisely similar. I have related this in order to show the mental calibre of the men with whom I was thrown in contact. Intellectually they were children, inhabiting the physical forms of men."
The Sea-Wolf by Jack London |
|
|
|
Igor2004
More clever than you
Canada
78 Posts |
Posted - 06/15/2005 : 10:46:32
|
to elwoos
"Firstly so that more clever developers than I could provide constuctive criticism should they choose."
Elimination of errors is 95-99 % of work, that is you publish your functions for the subsequent debugging (but you would not write about it).
I publish the functions for use in any application,
I guarantee that they work according to their description.
As it is possible to distinguish on the Internet of function published for debugging
(Clever developers should make debugging, by the way, how many advice concerning to your functions you have received from other developers)
and already checked up, good functions?
"If I want them to work in more general cases in the future I will have to review them"
The most part is already written and debugged, I have written the functions only because have not found anything similar.
---------------------------------------
btw, as our queen there lives :-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 06/15/2005 : 11:14:15
|
Well...for the most part, there's always more than one way to skin a cat
USE Northwind
GO
CREATE FUNCTION udf_Occurs(@SearchString varchar(8000), @SearchArg varchar(50))
RETURNS int
AS
BEGIN
RETURN (LEN(@SearchString)-LEN(REPLACE(@SearchString,@SearchArg,'')))/LEN(@SearchArg)
END
GO
SELECT dbo.udf_Occurs('abcdefghijklmnopabc','abc')
GO
DROP FUNCTION udf_Occurs
GO
Et on devrait toujours rechercher la solution effecient.... Non?
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 : 11:52:08
|
Dear Brett,
Why you are so inattentive ?
I wrote
-- Returns the number of times a character expression occurs within another character expression (include overlaps).
Here in Quebec there is a saying "I you have constructed that school in which you studied"
If you downloaded my functions that would not do this "discovery",
I have already received the letter with same "discovery", and errors.
select dbo.udf_Occurs('aau ', 'aau aau ') -- display 0 but Correct result is 2
reason is Len - Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
I wrote to Stephen Dobson
But your idea is very good, so
CREATE function OCCURS2 (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@cSearchExpression) > 0
then ( datalength(@cExpressionSearched)
- datalength(replace(@cExpressionSearched, @cSearchExpression, '')))
/ datalength(@cSearchExpression)
else 0
end
end
This code works correctly.
You agree with it ?
I am mathematician and I search for exceptions :-) .
Now
The above mentioned functions work similarly as case sensitive functions irrespective of installations of Collation Settings in your database, i.e. the symbols 'A' and 'a' differ, symbols 'e' and 'é' (e with accent aigu) differ !
CREATE function OCCURS2 (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@cSearchExpression) > 0
then ( datalength(@cExpressionSearched)
- datalength(replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@cSearchExpression as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@cSearchExpression)
else 0
end
end
You agree with it ?
select dbo.OCCURS3(N'ééu ', N'ééu eeu ') -- display always 2
select dbo.OCCURS2(N'ééu ', N'ééu eeu ') -- result depends from Collation Settings in your database
Your code (depending of strings) is faster from 10 up 300 percents.
It is made for reduction of quantity of a code.
But my idea to count the 'occurs' include overlaps !
I wrote:
-- Attention !!!
-- select dbo.OCCURS('ABCA', 'ABCABCABCA') -- display 3
-- 1 occurrence of substring 'ABCA .. BCABCA'
-- 2 occurrence of substring 'ABC...ABCA...BCA'
-- 3 occurrence of substring 'ABCABC...ABCA'
This is function of Stephen Dobson in my library
__________________________________________________________________________
-- Author: Stephen Dobson, Toronto, EMail: sdobson@acc.org
-- OCCURS2() User-Defined Function
-- Returns the number of times a character expression occurs within another character expression ( excluding overlaps).
-- OCCURS2(@cSearchExpression, @cExpressionSearched)
-- Return Values smallint
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies a character expression that OCCURS2() searches for within @cExpressionSearched.
-- @cExpressionSearched nvarchar(4000) Specifies the character expression OCCURS2() searches for @cSearchExpression.
-- Remarks
-- OCCURS2() returns 0 (zero) if @cSearchExpression is not found within @cExpressionSearched.
-- Example
-- declare @gcString nvarchar(4000)
-- select @gcString = 'abracadabra'
-- select dbo.OCCURS2('a', @gcString ) -- Displays 5
-- Attention !!!
-- This function counts the 'occurs' exclude overlaps !
-- select dbo.OCCURS2('ABCA', 'ABCABCABCA') -- display 2
-- 1 occurrence of substring 'ABCA .. BCABCA'
-- 2 occurrence of substring 'ABCABC... ABCA'
-- See Also OCCURS()
CREATE function OCCURS2 (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@cSearchExpression) > 0
then ( datalength(@cExpressionSearched)
- datalength(replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@cSearchExpression as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@cSearchExpression)
else 0
end
end
__________________________________________________________________________
"Et on devrait toujours rechercher la solution effecient"
Bien sur ! Je les ai trouve.
|
|
|
|
Topic |
|
|
|
User-Defined string Functions Transact-SQL
