作者
digoal
日期
2017-04-12
标签
PostgreSQL , 格式化 , 正则表达式 , 类型判断
背景
如何判断一个字符串是合法的数值、浮点或者科学计数的格式?
首先想到的是正则表达式。
一些匹配规则如下：
"^\d+$" //非负整数（正整数 + 0）
"^[0-9]*[1-9][0-9]*$" //正整数
"^((-\d+)|(0+))$" //非正整数（负整数 + 0）
"^-[0-9]*[1-9][0-9]*$" //负整数
"^-?\d+$" //整数
"^\d+(\.\d+)?$" //非负浮点数（正浮点数 + 0）
"^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$" //正浮点数
"^((-\d+(\.\d+)?)|(0+(\.0+)?))$" //非正浮点数（负浮点数 + 0）
"^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$" //负浮点数
"^(-?\d+)(\.\d+)?$" //浮点数
PostgreSQL支持正则表达，UDF函数，可以完成这项工作。
正文
将正则表达式写成函数即可完成对应的判断，例子
create or replace function check_int(text) returns boolean as $$
select $1 ~ '^\d+$';
$$ language sql strict;
验证
```
postgres=# select check_int('1');
check_int
t
(1 row)
postgres=# select check_int('123');
check_int
t
(1 row)
postgres=# select check_int('123.1');
check_int
f
(1 row)
postgres=# select check_int('');
check_int
f
(1 row)
postgres=# select check_int('abc');
check_int
f
(1 row)
```
如果不区分格式的话，可以使用PostgreSQL的强制转换以及函数来处理，使用异常捕获即可。
postgres=# create or replace function check_numeric(text) returns boolean as $$
declare
begin
perform ($1)::numeric;
return true;
exception when others then
return false;
end;
$$ language plpgsql strict;
CREATE FUNCTION
验证
```
postgres=# select check_numeric('12..1');
check_numeric
f
(1 row)
postgres=# select check_numeric('12.1');
check_numeric
t
(1 row)
postgres=# select check_numeric('12.1a');
check_numeric
f
(1 row)
```
如果你要强转异常的值，可以自定义cast进行转换，例子.
```
postgres=# select '12.1a.1'::text::numeric;
ERROR: invalid input syntax for type numeric: "12.1a.1"
postgres=# create or replace function text_to_numeric(text) returns numeric as $$
select to_number($1,'9999999999999999999999999.99999999999999');
$$ language sql strict;
CREATE FUNCTION
postgres=# select text_to_numeric('12.1a.1');
text_to_numeric
12.11
(1 row)
postgres=# create cast (text as numeric) with function text_to_numeric(text) ;
CREATE CAST
postgres=# select '12.1a.1'::text::numeric;
numeric
12.11
(1 row)
```
补充
``` 补充
//正则匹配
匹配中文字符的正则表达式： [\u4e00-\u9fa5]
匹配双字节字符(包括汉字在内)：[^\x00-\xff]
匹配空行的正则表达式：\n[\s| ]*\r
匹配HTML标记的正则表达式：/<(.)>.<\/\1>|<(.*) \/>/
匹配首尾空格的正则表达式：(^\s)|(\s$)（像vbscript那样的trim函数）
匹配Email地址的正则表达式：\w+([-+.]\w+)@\w+([-.]\w+).\w+([-.]\w+)*
匹配网址URL的正则表达式：http://([\w-]+.)+[\w-]+(/[\w- ./?%&=]*)? ```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
