資策會[T-SQL] 講義實作題：
條件與迴圈敘述句
檢查身分證字號是否合法？
----------------------------------------------------------------------------------
declare @before varchar(11);
declare @after varchar(11);
declare @const varchar(11) = '19876543211';--乘數
declare @res int = 0;
declare @counter smallint = 0;--計數
set @before = 'A100987638';
print substring(@before,1,1)--print A
set @after = case substring(@before,1,1)
when 'A' then '10'
when 'B' then '11'
when 'C' then '12'
when 'D' then '13'
when 'E' then '14'
when 'F' then '15'
when 'G' then '16'
when 'H' then '17'
when 'I' then '34'
when 'J' then '18'
when 'K' then '19'
when 'L' then '20'
when 'M' then '21'
when 'N' then '22'
when 'O' then '35'
when 'P' then '23'
when 'Q' then '24'
when 'R' then '25'
when 'S' then '26'
when 'T' then '27'
when 'U' then '28'
when 'V' then '29'
when 'X' then '30'
when 'Y' then '31'
end +substring(@before,2,9);--字串連結
--原本我的then後面的數字沒有寫成'10'，而是10，這樣會變成是：10 + '100987638'，
--執行後並未發生錯誤，代表編譯時已經將'100987638'自動轉型成int型態再跟10相加
print @after--print 100987648，當10為int(10 + 100987638)
--print 10100987638，當'10'為varchar('10' + '100987638')
while(@counter <= 11)
begin
--print cast(substring(@after,@counter,1) as int)
--print cast(substring(@const,@counter,1) as int)
set @counter = @counter + 1;
set @res = @res + (cast(substring(@after,@counter,1) as int) * cast(substring(@const,@counter,1) as int));
end
print @res;
--原先while條件寫成@res%10 = 0，是不會有正確結果的，
--試想我們需要將兩個字串在每次循環時取出個別的一個字符相乘，並將相乘的結果累加，
--而這兩個字串的長度均為11，所以很明顯地必須有11次的循環，循環條件為：@counter <= 11
如有敘述錯誤，還請不吝嗇留言指教，thanks!