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)