MSSQLMOD11-2身份证号码校对_某年某月_新浪博客

DECLARE @sfz NVARCHAR(100),@i INT,@m INT

SET @sfz='51343319831005429X'

SET @i=1

SET @m=len(@sfz)

WHILE @i<@m

BEGIN

SET @sfz=stuff(@sfz,2*@i,0,' ');

SET @i+=1;

END

--SELECT * FROM dbo.f_split(@sfz,' ');

SELECT sfz=replace(@sfz,' ',''),

last_s=CAST(RIGHT(@sfz,1) AS NVARCHAR(1)),

ck_s=CASE when(12 - (sum(s) % 11)) % 11=10 THEN 'X' ELSE CAST((12 - (sum(s) % 11)) % 11 AS NVARCHAR(1)) END,

jg=CASE WHEN CAST(RIGHT(@sfz,1) AS NVARCHAR(1)) = (CASE when(12 - (sum(s) % 11)) % 11=10 THEN 'X' 

  ELSE CAST((12 - (sum(s) % 11)) % 11 AS NVARCHAR(1)) END) THEN 'OK' ELSE 'PASS' END

FROM(

SELECT a.* ,

p=power(2,18-a.id) % 11,

s=CAST(a.a AS INT) * (power(2,18-a.id) % 11)

FROM dbo.f_split(@sfz,' ') AS a

WHERE a.id<=17

) AS b