南大通用GBase 8a通过18位身份证号码判断男女性别

18位的身份证,其倒数第2位区分男女,单数是男,双数是女。本文通过GBase 8a用SQL的方式来实现该功能的几个方法。

分析

从18位的数字,获得倒数第二位的数字,可以用字符串截取,也可以用数字操作。

判断奇偶,一般采用取模2的方式, %2 如果为0则是偶数,为1则是奇数。

实现代码

数字操作方法

注意,数字除以10后,默认是浮点数,要用floor函数取整,再取模。

gbase> select floor(123456789012345678/10)%2;
+--------------------------------+
| floor(123456789012345678/10)%2 |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select floor(123456789012345688/10)%2;
+--------------------------------+
| floor(123456789012345688/10)%2 |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

字符串截取方式

采用left, right函数相对简单些。右面取2位,再左面取1位。

gbase> select left(right(123456789012345678,2),1) % 2;
+-------------------------------------------+
| left(right('123456789012345678',2),1) % 2 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

截取substring的写法很繁琐,而且还要转成数字再做取模。

gbase> select cast(substr(123456789012345688,length(123456789012345688)-1,1) as int) % 2;
+----------------------------------------------------------------------------+
| cast(substr(123456789012345688,length(123456789012345688)-1,1) as int) % 2 |
+----------------------------------------------------------------------------+
|                                                                          0 |
+----------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select cast(substr(123456789012345678,length(123456789012345678)-1,1) as int) % 2;
+----------------------------------------------------------------------------+
| cast(substr(123456789012345678,length(123456789012345678)-1,1) as int) % 2 |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)