Please Share your Product Ideas with us!

dsw.dbo.boaAllCase() Incorrectly reports single character alpha as mixed case which by definition is impossible.

 

By removing the last IF clause the function behaves as expected but the if clause was obviously put there on purpose at some point. I truely beleve the final IF clause should be removed but should we decide to keep it we should document the reason within the function. At last thought if for some reason there is a reason to keep the current functionality, I would suggest we pass a second parameter to allow either functionality.

 

Update:

Since my intial post this morning I have found other flaws when dealing with NULL and blank values. I have adjusted the function as I feel it should work. Please consider the following:

 

USE [DSW]

GO

/****** Object:  UserDefinedFunction [dbo].[boaAllCase]    Script Date: 2/2/2015 5:08:35 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*Copyright (c) 2003 BackOffice Associates, Inc.  All rights reserved. */

/* Date   Programmer Mod No */

/* ----------------------------------------------------------------------- ----------------------------*/

/* 9/3/2004 Joe Gould   0   */

 

 

/* Returns a 0 if the case is mixed.  */

/* Returns a 1 if the case is uppercase.  */

/* Returns a 2 if the case is lowercase.  */

/* If the string is 1 or less characters, a 0 is returned */

/*  Example:  SELECT dbo.boaAllCase('Bob') returns a 0 */

/*  Example:  SELECT dbo.boaAllCase('BOB') returns a 1 */

/*  Example:  SELECT dbo.boaAllCase('bob') returns a 2 */

/*  Jeremiah Gilmore - Example:  SELECT dbo.boaAllCase('') returns a 3 */

/*  Jeremiah Gilmore - Example:  SELECT dbo.boaAllCase('     ') returns a 3 */

/*  Jeremiah Gilmore - Example:  SELECT dbo.boaAllCase(NULL) returns a NULL */

 

 

ALTER FUNCTION [dbo].[boaAllCase] (@String AS nvarchar(4000))  RETURNS integer AS   

BEGIN

 

 

DECLARE @Return AS integer

DECLARE @Upper AS nvarchar(4000)

DECLARE @Lower AS nvarchar(4000)

SET @Upper = UPPER(@String)

SET @Lower = LOWER(@String)

SET @Return = 0

IF CAST (@String AS varbinary(4000)) = CAST (@Upper AS varbinary(4000)) AND @String IS NOT NULL AND @String <> '' AND @String <> ' ' SET @Return = 1

ELSE IF CAST (@String AS varbinary(4000)) = CAST (@Lower AS varbinary(4000)) AND @String IS NOT NULL AND @String <> '' AND @String <> ' ' SET @Return = 2

ELSE IF REPLACE(@String,' ','')='' SET @Return = 3

ELSE IF @String IS NULL SET @Return=NULL

--IF len(@String) <= 1 AND DSW.dbo.boaIsAlpha(@String) = 1 SET @Return = 0

 

 

RETURN (@Return)

 

 

END