Validating VAT Numbers
-
Hi
Would anybody have any T-SQL script that validates UK (and european) VAT numbers to ensure they are the correct format? and I don't just mean field lengths but the actual validity of the numbering structure in the VAT number?
Thanks in advance!
-
Do you know how that algorithm works?
(as I have no clue..)
/Kenneth
-
works like this...
http://www.vatliaison.com/vatinfo/vat_registration.html
just wondering if anybody out there has written code to do it already
-
David,
Here's a T-SQL function that checks european VAT numbers (excluding UK) are formatted correctly. I'd double check the logic as I quickly knocked this up from vb.net code. UK Customs and Excise provide a VAT handbook that issues guidelines on what is a valid VAT number for each of the european states - there might be something on their website. Alternatively, if you're not in the UK I imagine your national customs and excise (or equivalent) will have this info.
Hope this helps anyway.
create
function IsVatValid(@country varchar(40), @vat_number varchar(12))
returns
bit
as
/*
e.g. select dbo.IsVatValid('Austria','U1234567')
*/
begin
declare @is_valid bit
set @is_valid = 1
if @country = 'Austria'
begin
if len(@vat_number) <> 8 or upper(substring(@vat_number,1,1)) <> 'U' or isnumeric(substring(@vat_number,2,7)) = 0
set @is_valid = 0
end
else if @country = 'Belgium'
begin
if len(@vat_number) <> 9 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Denmark'
begin
if len(@vat_number) <> 8 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Finland'
begin
if len(@vat_number) <> 8 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'France' or @country = 'Monaco'
begin
if len(@vat_number) <> 11 or isnumeric(substring(@vat_number,3,8)) = 0 or charindex('O',@vat_number) = 0 or charindex('I',@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Germany'
begin
if len(@vat_number) <> 9 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Greece'
begin
if len(@vat_number) <> 9 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Ireland'
begin
if len(@vat_number) <> 8 or isnumeric(substring(@vat_number,3,5)) = 0 or isnumeric(substring(@vat_number,1,1)) = 0
set @is_valid = 0
end
else if @country = 'Italy'
begin
if len(@vat_number) <> 11 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Luxembourg'
begin
if len(@vat_number) <> 8 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Netherlands'
begin
if len(@vat_number) <> 8 or isnumeric(substring(@vat_number,1,9)) = 0 or isnumeric(substring(@vat_number,11,2)) = 0
set @is_valid = 0
end
else if @country = 'Portugal'
begin
if len(@vat_number) <> 9 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else if @country = 'Spain'
begin
if len(@vat_number) <> 8 or isnumeric(substring(@vat_number,2,7)) = 0
set @is_valid = 0
end
else if @country = 'Sweden'
begin
if len(@vat_number) <> 12 or isnumeric(@vat_number) = 0
set @is_valid = 0
end
else
set @is_valid = 0
return @is_valid
end
-
Incidentally,
is there any reason why you don't want to do this at the application tier? I'd imagine you're wanting to do this for an application. If so, it might make more sense to carry out this logic there rather than make a roundtrip to the database server.
Then again, your implementation might well require this logic to be on the server
. -
Basically the purpose is to look at historic data and highlight any invalid formats to the client, where if required they can place any necessary controls to prevent invalid VAT numbers being inputted....
Old hand, am I right in saying that the code you provided looks at the format only? rather than the logic of the numbering to constitute a valid VAT number...?
Thanks
-
David,
That's right. The code only looks at the format. To be honest, the customs and excise guide book only provided this - it didn't provide any details on what numerical logic constitutes a valid vat number. So it might well be that there is no numerical logic (that's the way I understood it) or, if there is a logic then it wasn't included in their handbook (or I completely missed it).
-
Cheers Karl
There is some logic for UK VAT numbers (which I only found out today) , its detailed here:
http://www.vatliaison.com/vatinfo/vat_registration.html
-
Thanks for that. In my case, I'm not too worried about UK vat numbers but it's useful to know.
-
David
This function should be what you're looking for. It returns 1 if the number is valid, and 0 otherwise. It only does the mathematical algorithm part, so you'll need to use a separate function to check the format, or add further logic at the beginning of this one.
John
CREATE FUNCTION CheckVAT (@VATNo VARCHAR(11))
RETURNS bit
AS
BEGIN
SET @VATNo = REPLACE(@VATno, ' ', '')
DECLARE @table TABLE(Digit tinyint, Multiplier tinyint, Result smallint NULL)
DECLARE @i tinyint
DECLARE @sum smallint
DECLARE @negative smallint
DECLARE @negativestr CHAR(2)
DECLARE @endtwo CHAR(2)
DECLARE @YesNo bit
DECLARE @digit CHAR(1)
SET @i = 1
WHILE @i < 8
BEGIN
SET @digit = SUBSTRING(@VATNo, @i, 1)
INSERT INTO @table (Digit, Multiplier) VALUES (CAST(@digit AS tinyint), 9 - @i)
SET @i = @i + 1
END
UPDATE @table SET Result = Digit * Multiplier
SELECT @sum = SUM(Result) FROM @table
SET @negative = CEILING(@sum / 97.0) * 97 - @sum
SET @negativestr = CASE
WHEN @negative < 10 THEN '0' + CAST(@negative AS CHAR(1))
ELSE CAST(@negative AS CHAR(2))
END
SET @endtwo = RIGHT(@VATNo, 2)
SET @YesNo = CASE @endtwo
WHEN @negativestr THEN 1
ELSE 0
END
RETURN(@YesNo)
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply