## 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 `