PostgreSQL版的身份證號碼15位轉18位

楓葉梨花發表於2018-05-04
SQL

最近公司為了節約成本使用了開源資料庫PostgreSql，然後需要把Oracle的相關的sql語句轉換為pgsql的語法，其中有一個函式是IDCARD15TO18,即將身份證號碼15位轉成18位，百度了一圈並沒有可以複製，只能自己改造了。

本文參考了以下部落格，十分感謝：

Oracle常用函式:IDCARD15TO18() 身份證15位轉18位函式

SQL程式碼

CREATE OR REPLACE FUNCTION idcard15to18("card" varchar)
  RETURNS varchar AS $$
DECLARE
    RESULTS varchar(18);
    W       INTEGER ARRAY[18]; --數字陣列
    A       CHAR ARRAY[11]; --字元陣列
    S       Integer;
Begin
    If CARD Is Null Then
        Return '';
    End If;	
    If LENGTH(CARD) <> 15 Then
        Return CARD;
    End If;
    W       := array[7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1];
    A       := array['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'];
    RESULTS := SUBSTR(CARD, 1, 6) || '19' || SUBSTR(CARD, 7, 10);
		S       := 0;
		Begin
        For I In 1 .. 17 Loop
            S := S + to_number(SUBSTR(RESULTS, I, 1),'9') * W[I];
        End Loop;
    Exception
        When Others Then
            Return '';
    End;
    S       := mod(S,11);
    RESULTS := RESULTS || A[S + 1];
    Return(RESULTS);
End;
$$ LANGUAGE plpgsql;
複製程式碼

注意點

定義陣列

W INTEGER ARRAY[18]
S CHAR ARRAY[11]
複製程式碼

陣列賦值

 W := array[7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1];
複製程式碼

字串轉數字

//to_number(str,'9') 考慮到只有一位整數，所以只需一個9即可
to_number(SUBSTR(RESULTS, I, 1),'9')
複製程式碼

求餘

//區分ORACLE的 S mod 11
 S := mod(S,11);
複製程式碼

相關文章