Tech Tips

	Home

	Tech Stuff

	Financial

	Contact Us

Tech Stuff

Welcome to the Berry-Tech technical "stuff" page. This is a collection of code snippets that have proven useful. It's a work in progress, so check back for updates. You are welcome to use any code here as you wish!!!

SQL Server

Mod 10 User-Defined Function
Accepts up to a 50-digit number and returns the same number with a Mod10 check-digit appended. (You may want to add validation to ensure the number passed in is numeric.)

/******************************************************************************
**
**  Name: Mod10
**
**  Description: 
**		Takes in a numeric string and appends a check digit
** 		using the Mod10 algorithm.
**
**  History:
**		02/10/2005 - Jeff Berry - created
******************************************************************************/
CREATE FUNCTION dbo.Mod10 
(
@NumericIn VARCHAR(50)
)
RETURNS VARCHAR(51) AS  
BEGIN 
	DECLARE @Len INT
	DECLARE @iLoop INT
	DECLARE @iSum INT
	DECLARE @iWork INT
	DECLARE @iCheckDigit INT
	
	SET @NumericIn = LTRIM(RTRIM(@NumericIn))

	SET @iSum = 0
	SET @Len = LEN(@NumericIn)
	
	SET @iLoop = @Len
	--Process digits from right to left
	WHILE @iLoop > 0
	BEGIN
		--If any character is not a digit, fail
		IF ISNUMERIC(SUBSTRING(@NumericIn, @iLoop, 1)) = 0
		BEGIN
			RETURN ''
		END

		SET @iWork = CONVERT(INT, SUBSTRING(@NumericIn, @iLoop, 1))
		IF @iLoop % 2 = @Len % 2
		BEGIN
			SET @iWork = @iWork * 2
			SET @iSum = @iSum + FLOOR(@iWork / 10) + @iWork % 10
		END
		ELSE
		BEGIN
			SET @iSum = @iSum + @iWork
		END
	
		SET @iLoop = @iLoop - 1
	
	END
	
	--Check Digit is value that when added to @iSum makes it an even multiple of 10
	SET @iCheckDigit = 10 - @iSum % 10
	--If @iSum was already an even multiple of 10, then check digit is 0
	IF @iCheckDigit = 10
	BEGIN
		SET @iCheckDigit = 0
	END
	
	RETURN @NumericIn  + CONVERT(VARCHAR(1), @iCheckDigit)

END

Proper Case User-Defined Function
Accepts up to a 100-character string and returns a proper-case version of that string (ie all lower-case with the first letter of each word upper-case). Can easily be expanded for more than 100 characters if needed.

/******************************************************************************
**
**  Name: Proper
**
**  Description: 
**		Takes in a string and converts it to proper case.
**
**  History:
**		09/10/2005 - Jeff Berry - created from code samples found 
**			on internet.
******************************************************************************/
CREATE FUNCTION dbo.Proper (

    @tcString VARCHAR(100)
)   RETURNS VARCHAR(100)
AS BEGIN
-- Scratch variables used for processing
DECLARE @outputString VARCHAR(100)
DECLARE @stringLength INT
DECLARE @loopCounter INT
DECLARE @charAtPos VARCHAR(1)
DECLARE @wordStart INT

-- If the incoming string is NULL, return an error
IF (@tcString IS NULL)
RETURN ('(no string passed)')

-- Initialize the scratch variables
SET @outputString = ''
SET @stringLength = LEN (@tcString)
SET @loopCounter = 1
SET @wordStart = 1

-- Loop over the string
WHILE (@loopCounter <= @stringLength)
BEGIN
-- Get the single character off the string
SET @charAtPos = LOWER(SUBSTRING (@tcString, @loopCounter, 1))

-- If we are the start of a word, uppercase the character
-- and reset the word indicator
IF (@wordStart = 1)
BEGIN
SET @charAtPos = UPPER (@charAtPos)
SET @wordStart = 0
END

-- If we encounter a white space, indicate that we
-- are about to start a word
IF (@charAtPos = ' ')
SET @wordStart = 1

-- Form the output string
SET @outputString = @outputString + @charAtPos

SET @loopCounter = @loopCounter + 1
END

-- Return the final output
RETURN (@outputString)
END

Contact: 636-399-0633