User-Defined string Functions Transact-SQL

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

General SQL Server Forums

Script Library

User-Defined string Functions Transact-SQL

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 15:28:00

Ya know...I don't think I would Ever be able to build those functions if I needed them.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50370

You must be a very clever developer

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

78 Posts

Posted - 2005-06-11 : 20:24:56

Thanks, Brett, you are right, I am very clever developer.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1093197,00.html

http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
http://www.sqlservercentral.com/scripts/contributions/1488.asp
http://www.databasejournal.com/scripts/article.php/3503581
http://www.sqlserver.org.au/Resources/ViewResource.aspx?resourceId=38
http://www.developersdex.com/gurus/articles/790.asp
http://www.sswug.org/scriptmgr/recentscripts.asp
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8618
http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=5782
http://www.atoutfox.org/articles.asp?ACTION=FCONSULTER&ID=0000000188

etc

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-11 : 20:34:04

I wonder what was more difficult, writing the functions or finding all the forums?

Ironically, the list of links to the forums & resources might be more useful than the functions.
Thanks for the links Igor.

Edit: The functions too.

rockmoose

Igor2004
More clever than you

78 Posts

Posted - 2005-06-11 : 22:25:46

to rockmoose
You can find many links http://www.microsoft.com/sql/community/relcommunities.asp

To write good, robust, and faultless functions - it is very difficult.
Owing to a plenty of bad hand-made articles, clever people do not use "function", "library" in a plenty meeting in the Internet. You start something to put, and it is not established, as it is possible for it to trust. How it is possible to trust serious appendices to functions written it is not known whom? Therefore with scepticism meet also the above-stated functions, but they on the sly find the users, after the publication at serious forums the quantity of sceptics has come to naught, in essence object can nobody.
In 99.9 percentof cases function written by the layman will work correctly, you will spend a lot of time to find an error in 0.01 percent of cases!
For example it is necessary to understand a difference between len and datalength
It is necessary to know http://support.microsoft.com/kb/q310421/
etc
The letter received by me
----------------------------------------------------------------

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-11 : 22:38:57

quote:
Originally posted by Igor2004

Thanks, Brett, you are right, I am very clever developer.

very clever

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-12 : 08:15:04

Apologies, we had friendly sarcam week here at the Team.

rockmoose

Igor2004
More clever than you

78 Posts

Posted - 2005-06-12 : 12:10:37

to rockmoose
To apologize there is nothing I really very clever developer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-12 : 17:06:40

I'm sure you're just being modest.

quote:
Originally posted by Igor2004
...I really very clever developer

CODO ERGO SUM

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-12 : 20:47:08

I wish I could be clever. Really very clever would even be extremely better though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-13 : 03:31:55

I've written some string functions and got them published on the web. Does that mean I'm a clever developer?[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50044[/url]

<MentalNote>Must change the sig it's a bit of a give away<\MentalNote>

A sarcasm detector, what a great idea.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-13 : 04:18:52

wow!!... it seems that our members' sarcasm doubled since hog's sarcasm thread...

i get the feeling we let the beast out of the woods....

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-13 : 05:44:38

Spirit I hope you're not being sarcastic!!

steve

A sarcasm detector, what a great idea.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-13 : 06:41:14

not at all...

i've never been sarcastic in my life... i don't even know how it's done...

Go with the flow & have fun! Else fight the flow

Igor2004
More clever than you

78 Posts

Posted - 2005-06-13 : 10:25:54

to elwoos
I've written some string functions and got them published on the web. Does that mean I'm a clever developer?
-------------------------------------------------------
The answer - is not, for three reasons - some of your functions are erroneous
declare @v1 varchar(20), @v2 varchar(1)
select @v1='d g t ', @v2 = ' '
select dbo.fn_CountChars(@v1, @v2), dbo.OCCURS(@v2, @v1)
display 3 (The wrong answer) and 6 (Right answer)
Present what is the time will spend for search of a mistake each of who flew used it?
I can spend a heap of time and look for errors in other functions,
But it is better if it is will made by you.
Besides that you have written the majority of that is possible to make my functions.
In general it is necessary to be very attentive at use LEN

The some work with essential restrictions
There is a greater difference between @Delimiter varchar(1) and @Delimiter varchar(256)

It is possible to write the some people essentially more shortly, for instance

-- -- ' returns all the characters up to the delimiter
CREATE FUNCTION dbo.fn_FirstWord
(@LookIn varchar(4000),
@Delimiter varchar(1) = ' ')
RETURNS varchar(4000)
AS
BEGIN
select @Delimiter = isnull(@Delimiter,'')
RETURN left(@LookIn,CHARINDEX(@Delimiter, @LookIn))
END
GO

CREATE function fn_CountChars(@LookIn varchar(4000), @LookFor varchar(1))
returns smallint
as
begin
return
case
when datalength(@LookFor) > 0
then (datalength(@LookIn) - datalength(replace(@LookIn, @LookFor, '')))
/ datalength(@LookFor)
else 0
end
end

I force once again to repeat:"To write good, robust, and faultless functions - it is very difficult.
Owing to a plenty of bad hand-made articles, clever people do not use "function", "library" in a plenty meeting in the Internet. How it is possible to trust serious applications to functions written it is not known whom? Therefore with scepticism meet also the above-stated functions, but they on the sly find the users, after the publication at serious forums the quantity of sceptics has come to naught, in essence object can nobody."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-13 : 10:46:12

Got to love it...
This proved to be a really funny thread after all

(no offense to anyone)

>> To write good, robust, and faultless functions - it is very difficult
hmmm.....
You think Igor ?

Steve,
sorry, flunk on 3 aspects..., but I still think You are quite clever!!!

(PS. Igor, I also want your translator device, what is it?)

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 11:05:11

How's this week shaping up?

I think I got alittle more clever this weekend...I mean, hey, I didn't fall off the roof cleaning the gutters on Sunday

...man it was hot up there and all...being so much closer to the sun.

I must say however...never where khakis on the roof...

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-13 : 11:38:22

You Canadians sure have a clever way of expressing yourselves.

quote:
Originally posted by Igor2004

I force once again to repeat:"To write good, robust, and faultless functions - it is very difficult.
Owing to a plenty of bad hand-made articles, clever people do not use "function", "library" in a plenty meeting in the Internet. How it is possible to trust serious applications to functions written it is not known whom? Therefore with scepticism meet also the above-stated functions, but they on the sly find the users, after the publication at serious forums the quantity of sceptics has come to naught, in essence object can nobody."

CODO ERGO SUM

Igor2004
More clever than you

78 Posts

Posted - 2005-06-13 : 11:41:42

to rockmoose
>> To write good, robust, and faultless functions - it is very difficult
hmmm.....
You think Igor ?
I am assured of this, the majority will write some functions for 15 minutes, the given functions will work correctly in 99.9 percent of cases though the code will be ugly and superfluous. Search of exceptions and debugging, writing of a beautiful code demand a lot of time. The example of functions written can be seen in this topic, the author is assured that he has written good functions, same as well as my functions, the majority of authors are assured that they can to write good functions, I had to spend time to find in them mistakes, or to show as it is possible to reduce quantity of lines of a code.
The most part of work is not 15 minutes on a writing - the most part of work is debugging, and improvement of a code.

(PS. Igor, I also want your translator device, what is it?)
This is the translator from Russian on other languages and on the contrary,
Many use the translator on the Internet
http://translation.paralink.com/translation.asp
Unfortunately I do not know the translator from Swedish.

Igor2004
More clever than you

78 Posts

Posted - 2005-06-13 : 11:50:07

to Michael Valentine Jones
Unfortunately I don't speak english, I recently immigrated to Quebec, I speak russian and french. We discussed qualities of the developer, I never spoke that I well speak english.
But the general sense of the text I hope is clear.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-13 : 12:05:20

Tr¨¨s en rassurant pour entendre dire que Vous passez assez de temps pour d¨¦boguer et ¨¦valuer Votre code, pour que ce soit la preuve de faute de cent pour-cent. (et aussi beau). Que d'autres langues aimez-vous coder dans, sauf SQL ?
Soyez bienvenus ¨¤ SqlTeam et j'attends plus de contributions impatiemment de Votre partie!

Testing russian alphabet:

§°§é§Ö§ß§î §Ù§Ñ§Ó§Ö§â§ñ§ñ, §é§ä§à§Ò§í §ã§Ý§í§ê§Ñ§ä§î, §é§ä§à §£§í §ä§â§Ñ§ä§Ú§ä§Ö §Õ§à§ã§ä§Ñ§ä§à§é§ß§à §Ó§â§Ö§Þ§Ö§ß§Ú, §é§ä§à§Ò§í §à§ä§Ý§Ñ§Õ§Ú§ä§î §Ú §á§â§à§Ó§Ö§â§Ú§ä§î §£§Ñ§ê §Ü§à§Õ§Ö§Ü§ã, §ä§Ñ§Ü, §é§ä§à§Ò§í §ï§ä§à §Ò§í§Ý§à §Õ§à§Ü§Ñ§Ù§Ñ§ä§Ö§Ý§î§ã§ä§Ó§à §à§ê§Ú§Ò§Ü§Ú §ß§Ñ §ã§ä§à §á§â§à§è§Ö§ß§ä§à§Ó. (§Ú §ä§Ñ§Ü§Ø§Ö §Ü§â§Ñ§ã§Ú§Ó§í§Û). §¹§ä§à §Õ§â§å§Ô§Ú§Ö §ñ§Ù§í§Ü§Ú §£§í §Ý§ð§Ò§Ú§ä§Ö §Ù§Ñ§Ü§à§Õ§Ú§â§à§Ó§Ñ§ä§î §Ó, §Ü§â§à§Þ§Ö SQL?
§¥§à§Ò§â§à §á§à§Ø§Ñ§Ý§à§Ó§Ñ§ä§î §Ó SqlTeam, §Ú §ñ §ã §ß§Ö§ä§Ö§â§á§Ö§ß§Ú§Ö§Þ §Ø§Õ§å §Ò§à§Ý§î§ê§Ö§Ô§à §Ü§à§Ý§Ú§é§Ö§ã§ä§Ó§Ñ §Ó§Ü§Ý§Ñ§Õ§à§Ó §à§ä §£§Ñ§ê§Ö§Û §é§Ñ§ã§ä§Ú!

EDIT: russian did not work, and french accents nonplus

rockmoose

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-13 : 13:39:21

Perhaps I'm too clever for my own good

quote:
select @v1='d g t ', @v2 = ' '
select dbo.fn_CountChars(@v1, @v2), dbo.OCCURS(@v2, @v1)
display 3 (The wrong answer) and 6 (Right answer)

I don't see why 3 is the wrong answer, there are three spaces in @v1? I figured if I included everything in the string I would use LEN. As for the rest I never claimed it was pretty (or bug free)!

steve

A sarcasm detector, what a great idea.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources