Mysql Regex替换ipv4中的0 [英] Mysql Regex to replace 0 from ipv4
问题描述
我无法制作一个mysql regexp，它将在IPv4的每3个数字序列的开始中删除零. 问题是应该在数据库级别提供验证，而我不知道该如何制定用来自我的IPv4的emtpy字符串替换所有0.，00.，.0，.00的正则表达式.
I cannot make a mysql regexp which would remove zeros in the beggining of each 3 number sequences of IPv4. The problem is that the validation should be provided on database level and I don't know how to formulate the regex which would replace all 0., 00., .0, .00 with emtpy string from my IPv4.
我希望我的正则表达式执行类似的操作:
I want my regex to do something like that :
如果我有012.123.123.023输入数据库，则应将其另存为12.123.123.23
If I have 012.123.123.023 entering the database it should be saved as 12.123.123.23
谢谢！
推荐答案
By utilizing the MySQL functions INET_ATON() and INET_NTOA() you can reliably convert an incoming IPv4 address which has leading zeros into the same string without leading zeros. Wrap INET_ATON() with INET_NTOA() to convert the IP address first to its integer value, and then back to a dotted quad.
mysql> SELECT INET_NTOA(INET_ATON('001.110.011.111'));
+-----------------------------------------+
| INET_NTOA(INET_ATON('001.110.011.111')) |
+-----------------------------------------+
| 1.110.11.111 |
+-----------------------------------------+
并且没有前导零进行比较:
mysql> SELECT INET_NTOA(INET_ATON('1.110.11.111'));
+--------------------------------------+
| INET_NTOA(INET_ATON('1.110.11.111')) |
+--------------------------------------+
| 1.110.11.111 |
+--------------------------------------+
注意:如果输入的IP地址不是有效地址，这将返回NULL.它不会从错误的IP地址返回原始字符串或带前导零的行:
Note: This will return NULL if the input IP address was not a valid address. It won't return the original string or strip leading zeros from a bad IP address:
mysql> SELECT INET_NTOA(INET_ATON('888.777.123.123'));
+-----------------------------------------+
| INET_NTOA(INET_ATON('888.007.123.123')) |
+-----------------------------------------+
| NULL |
+-----------------------------------------+
这篇关于Mysql Regex替换ipv4中的0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
