Monday, September 10, 2007

Portuguese Fiscal Number Validator Methods

In the project that i'm now evolved, which is in the financial area, we need to choose a primary key for our customers table. Since all our customers should have, what we call in portuguese "número de contribuinte", Portuguese Fiscal Number or Tax ID, translated to regular english, we've decided to use this field as our primary key.

To start, we know that this number is unique to each customer, which is a good start. We also know that the number shoul be composed by nine (9) digits. But the most important thing is to have a mechanism to validate the number, according the portuguese law. After some googling with no results, we've decided to create the code to validate it.

------------------------------------------------------------------------------------
The function in MS SQL Server 2005:
------------------------------------------------------------------------------------


CREATE FUNCTION IsNifValid(@contrib varchar(50))
RETURNS BIT
AS
BEGIN
DECLARE @s varchar(50)
DECLARE @c char(1)
DECLARE @i int
DECLARE @checkDigit int
DECLARE @result bit

SET @result = 0
SET @s = REPLACE(@contrib,' ','')

IF (LEN(@s) = 9 AND ISNUMERIC(@s) = 1)

BEGIN

SET @c = left(@s,1)

IF (@c = '1' OR @c = '2' OR @c = '5' OR @c = '6' OR @c = '8' OR @c = '9')

BEGIN

SET @checkDigit = 0

SET @i = 1

WHILE (@i <= 8) BEGIN SET @checkDigit = @checkDigit + CONVERT(int, SUBSTRING(@s,@i,1)) * (10 - @i) SET @i=@i+1 END SET @checkDigit = 11 - @checkDigit % 11 IF (@checkDigit >= 10) SET @checkDigit = 0

IF @checkDigit = CONVERT(int, SUBSTRING(@s, 9, 1)) SET @result = 1

END

END

RETURN @result

END

No comments: