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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/13/2005 :  15:00:22  Show Profile  Reply with Quote
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
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/13/2005 :  22:17:19  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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?"
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/13/2005 :  23:16:48  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 06/13/2005 :  23:18:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
No no no, that would defeat his entire purpose. He's just supposed to LINK to his code, not actually post it.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/13/2005 :  23:19:56  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 06/13/2005 :  23:20:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/14/2005 :  01:03:19  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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 .
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/14/2005 :  03:17:02  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 06/14/2005 :  07:17:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
And keep up the crossposting! 'Cause shit, we just don't get enough of that here already.
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/14/2005 :  12:07:54  Show Profile  Visit Igor2004's Homepage  Reply with Quote
to spirit1
Thanks for your opinion.
"they're just having fun at your expense"
To splash by tongue everyone is able, to write something costing everyone isn't able.
to robvolk
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49733&whichpage=2
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/14/2005 :  12:36:04  Show Profile  Reply with Quote
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
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/14/2005 :  22:07:39  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  02:27:06  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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.


Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  02:35:20  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/15/2005 :  02:38:57  Show Profile  Reply with Quote
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.
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 06/15/2005 :  03:20:29  Show Profile  Reply with Quote
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.
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  10:09:37  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  10:46:32  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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 :-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/15/2005 :  11:14:15  Show Profile  Reply with Quote
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
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/15/2005 :  11:52:08  Show Profile  Visit Igor2004's Homepage  Reply with Quote
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.

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.25 seconds. Powered By: Snitz Forums 2000