So basically i have an ic column in my db: ic only contains numbers but the field is a string so people can add passport also. i want to write a query where it only shows me ic which are only numbers:
所以基本上我的数据库中有一个ic列:ic只包含数字,但字段是一个字符串,所以人们也可以添加护照。我想写一个查询,它只显示我的ic只有数字:
SELECT applications.ic
FROM applications
WHERE applications LIKE '%[^0-9]%'
But it dosent work!
但它起作用了!
1 个解决方案
#1
8
PostgreSQL supports POSIX regular expressions:
PostgreSQL支持POSIX正则表达式:
SELECT applications.ic
FROM applications
WHERE applications.ic ~ '^\d+$'
The regular expression here is:
这里的正则表达式是:
-
^— matches the beginning of the string -
\d— matches a digit -
+— one or more modifier applied to the previous matcher -
$— matches the end of the string
^ - 匹配字符串的开头
\ d - 匹配一个数字
+ - 应用于上一个匹配器的一个或多个修饰符
$ - 匹配字符串的结尾
#1
8
PostgreSQL supports POSIX regular expressions:
PostgreSQL支持POSIX正则表达式:
SELECT applications.ic
FROM applications
WHERE applications.ic ~ '^\d+$'
The regular expression here is:
这里的正则表达式是:
-
^— matches the beginning of the string -
\d— matches a digit -
+— one or more modifier applied to the previous matcher -
$— matches the end of the string
^ - 匹配字符串的开头
\ d - 匹配一个数字
+ - 应用于上一个匹配器的一个或多个修饰符
$ - 匹配字符串的结尾