Create a simple user defined function that checks if all characters in string are capital or not

In this post i will try to give a simple example of how to create a simple User Defined Function. The purpose of this function is to check if all characters in a string are capitals or not. It should ignore characters like “#,!,@” e.t.c.

We must launch SQL Server 2005. and then just select a database e.g Northwind,Pubs.

In a new query window we type the function(check_capitals) written below. After the query executes successfully we can see the new function under “Programmability”->”Functions”->”Scalar-valued Functions”

I will try to explain what i am trying to do.

This function takes an argument that it is our string and then checks every letter of the string if it belongs to the 65-90 values of the ASCII table. These values(65-90) belong to the capital letters from capital “A” to capital “Z”. if all letter ascii values of the string fall within the range above, I return a value “T”=true, else even if only fails then i return “F”=false. The function should ignore any characters like “@,!,$”. To accomplish this task we use the built-in functions of SQL Server like ASCII,DATALENGTH,SUBSTRING.

to test the function just write the following sql

 

SELECT dbo.check_capitals(‘Nick’) -returns F

SELECT dbo.check_capitals(‘NICK’) -returns T

SELECT dbo.check_capitals(‘NIC!K’) -returns T

 

function check_capitals 

******************************************************************************************** 

CREATE FUNCTION check_capitals (@String VARCHAR(500))

  RETURNS char(1) AS

BEGIN 

DECLARE @returns char(1)

DECLARE @place TINYINT

SET @place =1

WHILE @place <= DATALENGTH(@string)

BEGIN

IF (ASCII(SUBSTRING(@string, @place, 1)) BETWEEN 65 AND 90) OR (ASCII(SUBSTRING(@string, @place, 1)) BETWEEN 32 AND 64)

SELECT @returns = ‘T’

ELSE

SELECT @returns = ‘F’

 

IF @Returns <> ‘F’  

SET @place = @place +1

ELSE

GOTO

Exitfunction

END

 

Exitfunction:

 RETURN

 @returns

 END

 ************************************************************* 

 

About these ads