Alphanumeric and Special Character Validation

,

Alphanumeric and Special Character Validation
=========================================
/*
Function name: fn_ValidateString
Description : This function is used to validate whether the given string contains Alphanumeric character and some special character’s ‘@’ ,’(‘, ‘ )’, ‘_’, ‘-‘

Parameters: 1) @InputString varchar(25)
Accepts the string, which we need to validate

2) @Type int
= 0 validate the string for only alphanumeric characters
i.e Allows characters between [A-Z] ,[a-z] and [0-9]

= 1 validate the string for alphanumeric and special characters - ‘@’ ,’(‘, ‘ )’, ‘_’, ‘-‘
Return type: int

If the passed string is valid, it returns 1
Else 0

Usage:
1) Select dbo.fn_ValidateString('asd(a3)3_3AAAXXX-3',0)
Return 0 – Failure
Because it contains special characters (, )

2) Select dbo.fn_ValidateString('asda333AAAXXX3',0)
Returns 1 – Success only alpha numerics

3) Select dbo.fn_ValidateString('asd(a3)3_3AAAXXX-3',1)
Return 1 – Success


4) Select dbo.fn_ValidateString('asd(a3)3_3AAAXXX-3%''^&',1)

Return 0 – Failure

*/

Create Function fn_ValidateString
(
--RETURN 1 - VALIDE
--       0 - FAILURE
	@InputString Varchar(25),
	@Type int -- =0 allowes only ALPHANUMERIC, <>0 ALPHANUMERIC AND @  (  ) _ -

)
/*
	Author : Ramesh Kondaparthy
	Version: Initial
*/
Returns int
AS
BEGIN
DECLARE @Status int
DECLARE @Source Varchar(25)
DECLARE @Character CHAR(1)
DECLARE @rowCnt CHAR(1)

SET @Source=@InputString
SET @Character =''

SET @Source = LTRIM(RTRIM(@Source))
DECLARE @MyTable Table (Val char(1))

DECLARE @Pos  INT
DECLARE @Len INT
SET @Pos = 0
SET @Len = 0
	

	SET @Pos = LEN(@Source)
	SET @Len = @Pos

	WHILE @Pos > 0
		BEGIN
			SET @Character = LEFT(@Source,1)
--			Print @Character

			INSERT INTO @MyTable VALUES (@Character)

			SET @Pos = @Pos - 1
			SET @Source = RIGHT(@Source, @Pos)

		END
--Select @Pos

IF @Type = 0
	Begin
		Select  @rowCnt  = @Len  - count(*) from @MyTable Where Val like '[A-Z]' or Val like '[a-z]' or Val like '[0-9]' 
	End	
Else
	Begin
		Select  @rowCnt  = @Len  - count(*) from @MyTable Where Val like '[A-Z]' or Val like '[a-z]' or Val like '[0-9]'  or Val='@'  or Val='('  or Val=')'  or Val='_'  or Val='-'  
	End

If @rowCnt <>0 --	Print 'FAILURE'
	SET @Status  = 0
Else --	PRINT 'SUCCESS'
	SET @Status  = 1

	RETURN(@Status )		
End

Rate

Share

Share

Rate