Validate Singapore NRIC (as SQL Function)

Validate Singapore NRIC (as SQL Function)

GO
/****** Object:  UserDefinedFunction [dbo].[ValidateNRIC]    Script Date: 08/29/2018 5:24:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Murali, [email protected]>
-- Create date: <2018-04-28 17:30:50.723>
-- Description:	<Function validates the supplied NRIC string>

-- Examples NRIC 
-- Series - S | DOB - Any => S3845529Z | S6432041F | S0956648A
-- Series - F | DOB - Any => F0867553T | F0407353M | F6271257X
-- Series - G | DOB - Any => G8775017K | G8747022P | G9268281T
-- Series - T | DOB - 2010 -> 2019 => T1062864J | T1628028Z | T1503187A
-- Series - T | DOB - 2000 -> 2009 => T0143822G | T0284137H | T0320799J
-- =============================================
ALTER FUNCTION [dbo].[ValidateNRIC] (@str VARCHAR(9)) returns BIT
AS
BEGIN

	DECLARE @icArray TABLE  ( item INT)
	DECLARE @weight  INT
	DECLARE @offset  INT
	DECLARE @icArray_0 VARCHAR(1)
	DECLARE @icArray_8 VARCHAR(1)
	DECLARE @temp INT
	DECLARE @st TABLE (rowid INT, value VARCHAR(1))
	DECLARE @fg TABLE (rowid INT, value VARCHAR(1))
	DECLARE @theAlpha VARCHAR
	DECLARE @returnValue BIT = 0
	
	IF(LEN(@str) = 9)
	BEGIN

		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 2, 1) as INT) * 2
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 3, 1) as INT) * 7
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 4, 1) as INT) * 6
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 5, 1) as INT) * 5
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 6, 1) as INT) * 4
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 7, 1) as INT) * 3
		INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 8, 1) as INT) * 2

		SELECT @weight = SUM(item) FROM @icArray

		SELECT @icArray_0 = SUBSTRING(@str, 1, 1), @icArray_8 = SUBSTRING(@str, 9, 1)
 
		IF(@icArray_0 = 'T' OR @icArray_0 = 'G') SET @offset = 4 ELSE SET @offset = 0
		SET @temp = (@offset + @weight) % 11;

		INSERT INTO @st SELECT'0','J'
		INSERT INTO @st SELECT'1','Z'
		INSERT INTO @st SELECT'2','I'
		INSERT INTO @st SELECT'3','H'
		INSERT INTO @st SELECT'4','G'
		INSERT INTO @st SELECT'5','F'
		INSERT INTO @st SELECT'6','E'
		INSERT INTO @st SELECT'7','D'
		INSERT INTO @st SELECT'8','C'
		INSERT INTO @st SELECT'9','B'
		INSERT INTO @st SELECT'10','A'

		INSERT INTO @fg SELECT '0', 'X'
		INSERT INTO @fg SELECT '1', 'W'
		INSERT INTO @fg SELECT '2', 'U'
		INSERT INTO @fg SELECT '3', 'T'
		INSERT INTO @fg SELECT '4', 'R'
		INSERT INTO @fg SELECT '5', 'Q'
		INSERT INTO @fg SELECT '6', 'P'
		INSERT INTO @fg SELECT '7', 'N'
		INSERT INTO @fg SELECT '8', 'M'
		INSERT INTO @fg SELECT '9', 'L'
		INSERT INTO @fg SELECT '10', 'K'

		IF (@icArray_0 = 'S' OR @icArray_0 =  'T') 
			SELECT @theAlpha = Value from @st where rowid = @temp;
		ELSE IF (@icArray_0 =  'F' OR @icArray_0 =  'G')
			SELECT @theAlpha = Value from @fg where rowid = @temp; 

		-- SELECT @theAlpha, @temp
		If(@icArray_8 = @theAlpha) SET @returnValue = 1

		--SELECT @returnValue

	END
	RETURN @returnValue
END