JoeGarrepy.com SQL Server T-SQL String Functions

Programming/
Tools
      Oracle
      JavaScript
      C#
      SQL Server

Fun Stuff
      Music
      Martial Arts

Other
      Main

DISCLAIMER

This disclaimer is intended to cover all software/freeware, and other various files which are downloadable through or from this site.
Joe Garrepy, isp, and web host can not be held responsible for any software downloaded from this site. All freeware is downloaded at your own risk. Software which is listed on this site is assumed to work properly, but may or may not have different effects on different computer systems. The same rules apply to these programs. Joe Garrepy assumes no responsibility for those unusual occurrences where software has adverse effects on users computer. Joe Garrepy does not warrant these programs/files, and as such, can offer no guidelines in which to use them. All freeware/software/files, trademarks, service marks, and copyrights are acknowledged as belonging to their owners. Any of these which the owner wishes to be removed will be done so immediately. By downloading any software or viewing pages in which software can be downloaded, you agree to be bound to this agreement.

JoeGarrepy.com SQL Server T-SQL String Functions

Since I've started working more heavily with SQL Server I decided the best way for me to learn T-SQL was to convert some code written in Oracle's PL/SQL to T-SQL. My first thought was to convert the Oracle String Package since I use it so heavily in code that I write. Since SQL Server doesn't have PACKAGES I've been working to move each function in the STR package in to a User Defined SQL Server Function. I've also decided to JavaDoc/PLDoc the Function in the hopes I can get WinDDL to work on SQL Server the same way it works on Oracle. I guess that would be a TSqlDoc?

So far I've converted the following:

Function Name	Returns?	Conversion Date	Converted?
SPLIT	VARCHAR	1/13/2006	COMPLETE
SPLIT_ARRAY			No Equivalent
SPLIT_PIPE	Result Set of VARCHAR	1/13/2006	COMPLETE
IS_UPPER_CASE	INT	1/17/2006	COMPLETE
IS_LOWER_CASE	INT	1/17/2006	COMPLETE
IS_MIXED_CASE	INT	1/17/2006	COMPLETE
IS_NUMBER			ISNUMERIC built-in
IS_DATE			ISDATE built-in

SPLIT T-SQL User Defined Function
DROP FUNCTION dbo.split GO --> Scalar User-Defined function version of split CREATE FUNCTION split (@in_del_field VARCHAR(4000), @in_pos INT, @in_del VARCHAR(4000) = ',') RETURNS VARCHAR(4000) AS BEGIN /** * <hr> * SPLIT Scalar User-Defined function JG 1/13/2006 * <hr> * The split function will take a delimted string value * and return a value from a location in the string. * <PRE> * FOR EXAMPLE... * * print dbo.split('franklin,delano,roosevelt',1,',') * print dbo.split('franklin,delano,roosevelt',2,',') * print dbo.split('franklin,delano,roosevelt',3,',') * GO * * THIS RETURNS... * * franklin * delano * roosevelt * * </PRE> * @param @in_del_field Delimited Text string passed to the function * @param @in_pos Position of value to return * @param @in_del Delimiter. DEFAULT value is "," * @headcom */ DECLARE @retVal VARCHAR(4000) DECLARE @varFldDel VARCHAR(4000) SET @varFldDel = @in_del_field + ',' DECLARE @i int SET @i = 0 DECLARE @varExit VARCHAR(5) SET @varExit = 'FALSE' DECLARE @intLastLen int SET @intLastLen = 1 -- IF @in_pos < 1 SET @retVal = 'Not Valid Option, 1 is the first occurance of an item' ElSE --> loop through till the correct column is found WHILE @varExit = 'FALSE' BEGIN SET @i = @i + 1 If @i > 1 BEGIN SET @varFldDel = SUBSTRING(@varFldDel,@intLastLen,8000) END If CHARINDEX(@in_del,@varFldDel) < 1 BEGIN RETURN NULL END -- SET @retVal = SUBSTRING(@varFldDel,1,(CHARINDEX(@in_del,@varFldDel)-1)) SET @intLastLen = LEN(@retVal) + 2 If @i = @in_pos BREAK END -- RETURN @retVal -- END GO print dbo.split('franklin,delano,roosevelt',1,',') print dbo.split('franklin,delano,roosevelt',2,',') print dbo.split('franklin,delano,roosevelt',3,',') GO
SPLIT_PIPE T-SQL User Defined Function
DROP FUNCTION dbo.split_pipe GO CREATE FUNCTION dbo.split_pipe (@in_del_field VARCHAR(4000), @in_del VARCHAR(4000) = ',') RETURNS @SplitResults TABLE(SplitItems VARCHAR(4000)) BEGIN /** * <hr> * SPLIT_PIPE Scalar User-Defined function JG 1/13/2006 * <hr> * The SPLIT_PIPE funciton works like an Oracle Pipelined Function * and is based on STR.SPLIT_PIPE. This User Defined function will * return a result set of the delimited string passed to it. The * function STR must exist for this function to work. * <PRE> * FOR EXAMPLE... * * select * from split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',') * * THIS RETURNS a result set of the alphabet * * </PRE> * @param @in_del_field Delimited Text string passed to the function * @param @in_del Delimiter. DEFAULT value is "," * @headcom / DECLARE @varSplit VARCHAR(4000) SET @varSplit = 'XXXNOTNULLXXX' DECLARE @i INT SET @i = 1 -- WHILE @varSplit IS NOT NULL BEGIN --> get split value SET @varSplit = dbo.split(@in_del_field,@i,@in_del) -- If @varSplit IS NULL BEGIN BREAK END --> insert split value into results set INSERT INTO @SplitResults VALUES (@varSplit) -- SET @i = @i + 1 -- END -- RETURN -- END GO select from split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',') GO
IS_UPPER_CASE T-SQL User Defined Function
DROP FUNCTION dbo.is_upper_case GO CREATE FUNCTION dbo.is_upper_case (@in_string VARCHAR(4000)) RETURNS INT AS BEGIN /** * <hr> * IS_UPPER_CASE Scalar User-Defined function JG 1/17/2006 * <hr> * Function will return an integer "1" if a string passed * to it has all upper case aphla characters. If it * does not then it will return a "0" * <PRE> * FOR EXAMPLE... * * print dbo.is_upper_case('JOE GARREPY') * print dbo.is_upper_case('Joe Garrepy') * GO * * </PRE> * @param @in_string String Value to be evaluated * @return Integer "1" for TRUE "0" for false * @headcom */ --> normal function parameters DECLARE @retNum INT SET @retNum = 3 DECLARE @i INT SET @i = 0 DECLARE @varExit VARCHAR(5) SET @varExit = 'FALSE' DECLARE @varSearch VARCHAR(4000) SET @varSearch = REPLACE(@in_string,' ','') DECLARE @intAscii INT -- IF @in_string IS NOT NULL BEGIN --> loop through, check each character for its ascii value WHILE @varExit = 'FALSE' BEGIN SET @i = @i + 1 SET @intAscii = ASCII(SUBSTRING(@varSearch,@i,1)) If @intAscii > 64 AND @intAscii < 123 AND @intAscii NOT IN (91,92,93,94,95,96) BEGIN If @intAscii <= 90 AND @intAscii >= 65 BEGIN SET @retNum = 1 END Else BEGIN SET @retNum = 0 BREAK END END If @i = LEN(@varSearch) BEGIN BREAK END END END -- RETURN @retNum -- END GO print dbo.is_upper_case('JOE GARREPY') print dbo.is_upper_case('Joe Garrepy')
IS_LOWER_CASE T-SQL User Defined Function
DROP FUNCTION dbo.is_lower_case GO CREATE FUNCTION dbo.is_lower_case (@in_string VARCHAR(4000)) RETURNS INT AS BEGIN /** * <hr> * IS_LOWER_CASE Scalar User-Defined function JG 1/17/2006 * <hr> * Function will return an integer "1" if a string passed * to it has all lower case aphla characters. If it * does not then it will return a "0" * <PRE> * FOR EXAMPLE... ( * print dbo.is_lower_case('joe garrepy') * print dbo.is_lower_case('Joe Garrepy') * GO * * </PRE> * @param @in_string String Value to be evaluated * @return Integer "1" for TRUE "0" for false * @headcom */ --> normal function parameters DECLARE @retNum INT SET @retNum = 3 DECLARE @i INT SET @i = 0 DECLARE @varExit VARCHAR(5) SET @varExit = 'FALSE' DECLARE @varSearch VARCHAR(4000) SET @varSearch = REPLACE(@in_string,' ','') DECLARE @intAscii INT -- IF @in_string IS NOT NULL BEGIN --> loop through, check each character for its ascii value WHILE @varExit = 'FALSE' BEGIN SET @i = @i + 1 SET @intAscii = ASCII(SUBSTRING(@varSearch,@i,1)) If @intAscii > 64 AND @intAscii < 123 AND @intAscii NOT IN (91,92,93,94,95,96) BEGIN If @intAscii <= 122 AND @intAscii >= 97 BEGIN SET @retNum = 1 END Else BEGIN SET @retNum = 0 BREAK END END If @i = LEN(@varSearch) BEGIN BREAK END END END -- RETURN @retNum -- END GO print dbo.is_lower_case('joe garrepy') print dbo.is_lower_case('Joe Garrepy')
IS_MIXED_CASE T-SQL User Defined Function
DROP FUNCTION dbo.is_mixed_case GO CREATE FUNCTION dbo.is_mixed_case (@in_string VARCHAR(4000)) RETURNS INT AS BEGIN /** * <hr> * IS_MIXED_CASE Scalar User-Defined function JG 1/17/2006 * <hr> * Function will return an integer "1" if a string passed * to it is mixed case. If it is not then it will return a "0" * <PRE> * FOR EXAMPLE... ( * print dbo.is_mixed_case('joe garrepy') * print dbo.is_mixed_case('Joe Garrepy') * GO * * </PRE> * @param @in_string String Value to be evaluated * @return Integer "1" for TRUE "0" for false * @headcom */ -- DECLARE @retNum INT SET @retNum = 0 -- If dbo.is_upper_case(@in_string) = 0 AND dbo.is_lower_case(@in_string) = 0 SET @retNum = 1 Else SET @retNum = 0 -- RETURN @retNum -- END GO print dbo.is_mixed_case('joe garrepy') print dbo.is_mixed_case('Joe Garrepy')

If you find any bugs or want to contact me I can be reached @ doughjoe77@hotmail.com.

JoeGarrepy.com SQL Server T-SQL String Functions

SPLIT T-SQL User Defined Function

SPLIT_PIPE T-SQL User Defined Function

IS_UPPER_CASE T-SQL User Defined Function

IS_LOWER_CASE T-SQL User Defined Function

IS_MIXED_CASE T-SQL User Defined Function