利用SQL驗證統一編號是否正確(台灣)

下面這一個SQL Function可以驗證公司的統一編號是否正確。




USE TTL_BPA_EAI
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'FN_CheckTaxID')
DROP FUNCTION FN_CheckTaxID
GO

CREATE FUNCTION FN_CheckTaxID
(
@TaxID NVARCHAR(50)
)RETURNS CHAR(1)
AS
BEGIN
--驗證統一編號是否正確
--@TaxID 欲驗證的統一編號
--如果驗證結果正確則回傳 '1' ELSE '0'
--2005/11/11 BRIAN

DECLARE @I INT

DECLARE @A1 INT
DECLARE @A2 INT
DECLARE @A3 INT
DECLARE @A4 INT
DECLARE @A5 INT

DECLARE @B1 INT
DECLARE @B2 INT
DECLARE @B3 INT
DECLARE @B4 INT
DECLARE @B5 INT

DECLARE @C1 INT
DECLARE @C2 INT
DECLARE @C3 INT
DECLARE @C4 INT

DECLARE @D1 INT
DECLARE @D2 INT
DECLARE @D3 INT
DECLARE @D4 INT
DECLARE @D5 INT
DECLARE @D6 INT
DECLARE @D7 INT
DECLARE @CD8 INT

--判斷長度
IF LEN(@TaxID) <> 8
RETURN '0'

--判斷字元
DECLARE @J INT
SET @J = 1
WHILE @J <= 8
BEGIN
IF CHARINDEX( SUBSTRING(@TaxID , @J , 1) , '0123456789') = 0
RETURN '0'
SET @J = @J + 1
END

--設定變數
SET @D1 = CAST(SUBSTRING(@TaxID , 1 , 1) AS INT)
SET @D2 = CAST(SUBSTRING(@TaxID , 2 , 1) AS INT)
SET @D3 = CAST(SUBSTRING(@TaxID , 3 , 1) AS INT)
SET @D4 = CAST(SUBSTRING(@TaxID , 4 , 1) AS INT)
SET @D5 = CAST(SUBSTRING(@TaxID , 5 , 1) AS INT)
SET @D6 = CAST(SUBSTRING(@TaxID , 6 , 1) AS INT)
SET @D7 = CAST(SUBSTRING(@TaxID , 7 , 1) AS INT)
SET @CD8 = CAST(SUBSTRING(@TaxID , 8 , 1) AS INT)

SET @C1 = @D1
SET @C2 = @D3
SET @C3 = @D5
SET @C4 = @CD8

SET @A1 = CAST(((@D2 * 2) / 10) AS INT )
SET @B1 = (@D2 * 2 ) % 10

SET @A2 = CAST(((@D4 * 2) / 10) AS INT )
SET @B2 = (@D4 * 2) % 10

SET @A3 = CAST(((@D6 * 2) / 10) AS INT )
SET @B3 = (@D6 * 2) % 10

SET @A4 = CAST(((@D7 * 4) / 10) AS INT )
SET @B4 = (@D7 * 4) % 10

SET @A5 = CAST(((@A4 + @B4) / 10) AS INT )
SET @B5 = (@A4 + @B4) % 10

--計算公式
IF (@A1 + @B1 + @C1 + @A2 + @B2 + @C2 + @A3 + @B3 + @C3 + @A4 + @B4 + @C4) % 10 = 0
RETURN '1'

IF @D7 = 7
BEGIN
IF (@A1 + @B1 + @C1 + @A2 + @B2 + @C2 + @A3 + @B3 + @C3 + @A5 + @C4 ) % 10 = 0
RETURN '1'
END

RETURN '0'
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.FN_CheckTaxID('01121111')
SELECT dbo.FN_CheckTaxID('22425662')

GO