Alphanumeric and Special Character Validation- New

,

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 @  (  ) _ -
)
RETURNS int
AS
/*
    Author : Ramesh Kondaparthy
    Version: 1.0
    Changes Made: Replaced table variable with Case statements.

*/
BEGIN
--SET NOCOUNT ON
DECLARE @Status int
SET @Status=0

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

DECLARE @Valide int


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

	WHILE @Pos > 0
		BEGIN
			SET @Character = LEFT(@Source,1)
	
			IF @Type=0
			Begin
				SELECT @Valide=
					CASE 
						WHEN @Character like '[A-Z]' OR @Character like '[a-z]'  OR @Character like '[0-9]'  THEN 0
						ELSE 1
					END
			End
			Else
			Begin
				SELECT @Valide=
					CASE 
						WHEN (@Character like '[A-Z]' OR @Character like '[a-z]'  OR @Character like '[0-9]'  
							OR @Character = '@'   OR @Character = '('   OR @Character = ')'   
							OR @Character = '-'  OR @Character = '_' )   THEN 0
  						ELSE 1
					END
			End	
			IF @Valide = 1 
				Begin
					Break
				End
			Else	

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

		END
IF @Pos=0 
	SET @Status  = 0
ELSE
	SET @Status  = 1

RETURN @Status  


End

Rate

Share

Share

Rate