Important alert: (current site time 7/30/2014 10:52:01 AM EDT)
 

winzip icon

_123 TSQL functions updated 09-08-2002

Email
Submitted on: 7/2/2002 2:52:50 AM
By: Joseph Gama 
Level: Intermediate
User Rating: By 22 Users
Compatibility: SQL Server 2000
Views: 105715
 
     This is a collection of 123 TSQL functions for professional, academic or learning purposes. There are many conversions hexadecimal/octal/binary/Roman numerals, mathematical functions such as hyperbolic, logic and trigonometric. Combinatorial functions such as combinations, permutations (factorial), arrangements. Other interesting functions include turning a number into plain English, Morse code, EBCDIC and vice-versa, Levenshtein Distance (linguistics), encryption, infinite precision division and number theory functions: primes, deficient, perfect, abundant, golden numbers. Validation: valid email, IP, ZIP code and many others. There are some useful string functions to count occurrences of a string within another, find a character position in a string from the end of the string, wrap, rewrap, unwrap a string, etc… Some functions are clones from Microsoft Access or VBA(IIF, date, time, IsNull, IsEmpty), others from Oracle (INITCAP, TRANSLATE, RPAD, LPAD, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY). Base convertion(10)+Combinatorial(6)+Algebra(9)+Numeric(14)+String(24)+Date(8) Comparison, validation(27)+Logic(11)+Trigonometric(5)+Hyperbolic(9)=123 total Levenshtein Distance algorithm-original developer: Michael Gilleland (thank you for allowing me to translate the code to TSQL J ) http://www.merriampark.com/ld.htm Tested on Microsoft SQL Server 2000, BigInt should be replaced with int, in order to work with other versions of Microsoft SQL Server or Sybase. Developed or translated to TSQL by Joseph Gama. Please report any bugs and they will be fixed ASAP!

 
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
--**************************************
-- for :_123 TSQL functions updated 09-08-2002
--**************************************
Coded by Joseph Gama
winzip iconDownload code

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com

 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.


Other 13 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

7/2/2002 12:42:15 PMLewis Moten

Pretty good collection! You may want to also spread these out into there own individual pieces of code so users can search for them easier. Some of these can be made to run a bit faster - but ... wow!
(If this comment was disrespectful, please report it.)

 
7/4/2002 7:09:07 PMDimitriosT.

I have to comment your posting and vote 5 for sure.I am kind of sorry that people didnot realize yet the value of true coding.I will keep your posting in my library like a precious object.Thank you.Not only because of your coding, but also your really precious 'real' profile.
But if majority needs more help, maybe do as Moten says.Split it so that it can be easier searched through.
Have the best luck Joseph Gama.
(If this comment was disrespectful, please report it.)

 
7/7/2002 12:25:13 AMJoseph Gama

Lewis,
Thank you for voting and for your interesting ideas, I will submit them separately, once I get more code done. I am also thinking about improving some functions.
Peace

(If this comment was disrespectful, please report it.)

 
7/7/2002 12:39:50 AMJoseph Gama

Dimitrios,
Thank you for voting and for your nice words, I really appreciate it.
Those functions are a learning tool that, I hope, will help other people into their path to a better understanding of TSQL.
Best of luck to you too,
peace

(If this comment was disrespectful, please report it.)

 
7/8/2002 2:57:12 AMJoseph Gama

A bug with CRYPTX8 was fixed and the code updated.
(If this comment was disrespectful, please report it.)

 
7/10/2002 6:04:32 PMJB

Love your functions -- I think I found a bug in the Levenshtein function -- it seems to work properly when the strings are of the same length, but does not calculate proper distance when the strings are of different lengths. Thanks!
(If this comment was disrespectful, please report it.)

 
7/15/2002 1:04:17 AMJoseph Gama

JB,
Thank you for voting and for your support. I fixed that bug, when the length of T>S it wouldn't work right. I have also made minor improvements ni other functions.
Peace
(If this comment was disrespectful, please report it.)

 
8/5/2002 12:05:58 PMRasputin

Joseph,

Your submissions are, sadly, rare finds on PSC. If only there were more high quality submissions like yours! But, the silver lining to that is the feeling of excitement I get when, after weeks and sometimes even months of searching, I stumble upon something really great, which is the case with your submissions.

Thank you for your immense contribution of value and professional quality.

On a scale from 1 to 5, you deserve a 6!

Thank you for sharing your high caliber work!

- Ras
(If this comment was disrespectful, please report it.)

 
8/11/2002 9:06:16 PMJoseph Gama

Rasputin,
Thank you for voting and for your nice words. It's great to know that you and many others are enjoying this code. Every month I will post something new and hopefuly it will be as useful as this one.
You are very welcome, I will keep on sharing code, because code is ideas and ideas can only florish if spread.
Peace,
Joseph
(If this comment was disrespectful, please report it.)

 
9/9/2002 12:05:48 AMJoseph Gama

Thank you Jonathan for finding a bug in the Levenshtein Distance: the dimensions of the array would cause a NULL to be returned when words longer than 10 char were used. It has been fixed and 4 new functions added: degrees to grads, degrees to radians and the inverse.
Peace,
Joseph
(If this comment was disrespectful, please report it.)

 
5/22/2003 5:25:26 AM

CHARINDEXREV(s1, s2) Returns the position of an occurrence of string s2 within s1, from the
end of string. Input: VARCHAR(255), VARCHAR(255); Output: INT

This description does not match the function itself
(If this comment was disrespectful, please report it.)

 
8/23/2003 5:09:38 AMRaghavendra Narayana

Just Great.
(If this comment was disrespectful, please report it.)

 
10/20/2003 8:02:14 AM

i would like a function that coverts currency amount in words in SQL SERVER 2000
EXAMPLE
123.22 AS ONE HUNDRED TWENTY THREE AND PAISA TWENTY TWO ONLY
(If this comment was disrespectful, please report it.)

 
1/6/2004 1:13:28 PM

You still have a small bug in the Levenshtein distance function. The variable @d should be 2601
characters to accommodate the 50 character strings. This is ( MaxStringLength + 1 ) ^ 2. The function
will return NULL if the strings are 50 characters.

Your technique of using a varchar() vector as an array is cool. I created two support functions --
PeekArray and PokeArray -- and modified the function to use these. I think it cleans it up a little.
Also, a quick test for equality to return 0 is useful especially with longer strings.

Your function library looks useful, although I have only really looked at the Levenshtein distance
function.

Brian.

(If this comment was disrespectful, please report it.)

 
2/19/2004 3:05:04 AMcQual

Thanks for some great functions. I had to amend VALIDEMAIL slightly as it fell over if it received a string with no '@' in it. But that was easy to rectify.

You still deserve a 5 though for sharing your efforts with the world.

All the best...
(If this comment was disrespectful, please report it.)

 
4/21/2004 8:56:27 AM

5!? you deserve 55 :D

Keep up the good work.

(If this comment was disrespectful, please report it.)

 
10/13/2004 4:02:48 AMTristan B. Astillero

keep up the good work. i believe you've helped a lot of people w/ your work. be proud of it. keep on smiling! :D
(If this comment was disrespectful, please report it.)

 
12/23/2004 7:12:27 PM

Can you please update the links I cant download nothing
(If this comment was disrespectful, please report it.)

 
1/3/2005 9:16:34 AM

Hi,
Was looking forward to downloading the file after reading the comments on here, but kept getting an error.
Can somebody fix the problem please.
Thanks
Nilesh
(If this comment was disrespectful, please report it.)

 
3/15/2005 4:31:21 PM

You ROCK!!
Thanx for the truly righteous code.
(If this comment was disrespectful, please report it.)

 
10/21/2005 4:34:49 AMInacio Mille

Thank's a lot from Brazil !!!



(If this comment was disrespectful, please report it.)

 
1/2/2006 12:35:08 AMrezak

good luck
(If this comment was disrespectful, please report it.)

 
6/25/2006 3:24:08 PMIra Whiteside

Excellent work , also I am putting your Levenshtein insode a SSIS 2005 Wrapper asa Data Flow Component. Have you looked at Jaro-Winkler
(If this comment was disrespectful, please report it.)

 
7/7/2006 4:44:15 PMPaul Guernsey Player

VALIDZIP and VALIDZIP9 both set the wrong variable for the result and appear to always return NULL.

It is Very Nice to have all these examples, though.
(If this comment was disrespectful, please report it.)

 
10/13/2006 7:46:03 AMRaj

Its really very helpful
Thank You very much
(If this comment was disrespectful, please report it.)

 
10/23/2006 6:06:07 PMparker

Good work!! I tested few functions and found a bug in Factorial code. It is used in several different other functions. FACT(0) =1 and your function return 0. Simple error - if number < 2, it return number instead it should return 1. I've couple other questions, is there is way I can email you? Thanks.
(If this comment was disrespectful, please report it.)

 
11/30/2006 6:58:21 AMZensoft RC

good work done
(If this comment was disrespectful, please report it.)

 
1/10/2007 3:47:42 AM

insert an empty date field

(If this comment was disrespectful, please report it.)

 
3/30/2007 2:08:36 PMSAinCA

ISINTNUMBER sure verifies eligible numeric digits but I'd suggest it returns 0 when it finds a negative sign anywhere other than the start of the string. It would be VERY useful to have a set of functions that not only validate for digits but return a typed scalar containing NULL when 'invalid' or the actual value for tinyint, int, bigint. Requires three UDFs but VERY useful when data is string and the need is to validate XML in SS2K and convert to a specific type.
BTW, consider this as a replacement for the loop:
BEGIN
DECLARE @bool bit
SET @bool=0
IF LEN(@s) > 0
AND PATINDEX(N'%[^0-9]%', CASE WHEN LEFT(@s,1) = '-' THEN STUFF(@s,1,1,'') ELSE @S END) = 0
SET @bool=1
return @bool
END
(If this comment was disrespectful, please report it.)

 
8/20/2007 11:35:58 AM

I think IsIntNumber is incorrect. It seems to actually be checking if there is any digit or minus sign in the string, not that all the characters are digits or the unary -.
Further, I think the unary + should be accepted.

While I believe that SAinCA's version works, I would be tempted to simplify things with a call to ISNUMERIC like:

BEGIN

IF ISNUMERIC(@s) = 0
RETURN 0

IF PATINDEX('%[^0-9+-]%', @s ) > 0
RETURN 0

RETURN 1
END

(If this comment was disrespectful, please report it.)

 
6/4/2008 6:33:27 PMBrad G Skidmore

I really like the Translate function to clone the Oracle one!
I did find a couple of things that needed tweaking...

1. If the target column contained characters that were not included in the search and replace, those characters would be drooped from the result.
2. The CHARINDEX was not CaSe SeNsItIvE.

Here is the updated Translate Function for anyone interested...
(I posted the entire Function under my profile as well)

--1.
SET @j=CHARINDEX(@c,@f COLLATE Latin1_General_CS_AS)

IF @j>0
BEGIN
SET @result=@result + SUBSTRING(@t,@j,1)
END
ELSE
BEGIN
--2.
SET @result=@result + @c
END

(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.