Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2006-12-11 : 07:01:51
|
| 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! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-11 : 07:18:28
|
| If you can define what VAT number formats do you exactly want, you can get some ideas here.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-12-11 : 07:23:07
|
you should also validate their "format" in the front end application. Do not allow garbage to get to the database. I understand you can also put this check in the DB to reject crap too.  [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 07:36:36
|
| [code]CREATE FUNCTION dbo.fnValidateVAT( @VAT VARCHAR(8000))RETURNS BITASBEGIN IF @VAT LIKE '%[^0-9]%' RETURN 0 DECLARE @Index SMALLINT, @Multiplier TINYINT, @Sum INT, @Plus TINYINT SELECT @Index = LEN(@VAT), @Multiplier = 1, @Sum = 0 WHILE @Index >= 1 SELECT @Plus = @Multiplier * CAST(SUBSTRING(@VAT, @Index, 1) AS TINYINT), @Multiplier = 3 - @Multiplier, @Sum = @Sum + @Plus / 10, @Sum = @Sum + @Plus % 10, @Index = @Index - 1 RETURN CASE WHEN @Sum % 10 = 0 THEN 1 ELSE 0 ENDEND[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Validating VAT Numbers