Select lpad(ip[1],3,'0')||'.'||lpad(ip[2],3,'0')||'.'||lpad(ip[3],3,'0')||'.'||lpad(ip[4],3,'0') From (Select string_to_array('12.23.25.6','.') As ip) as ips
或者: Select array_to_string(array[lpad(ip[1],3,'0'),lpad(ip[2],3,'0'),lpad(ip[3],3,'0'),lpad(ip[4],3,'0')],'.') From (Select string_to_array('12.23.25.6','.') As ip) as ips
mysql> delimiter // mysql> create function f_test_new(i_str varchar(500)) -> returns varchar(1000) -> begin -> declare v_result varchar(1000); -> declare v_temp_str varchar(50); -> set v_result=''; -> set i_str = concat(i_str,'.'); -> while instr(i_str,'.')>0 do -> set v_temp_str = left(i_str,instr(i_str,'.')-1); -> set v_temp_str = lpad(v_temp_str,3,'0'); -> set v_result = concat(v_result,v_temp_str,'.'); -> set i_str = right(i_str,char_length(i_str)-instr(i_str,'.')); -> end while; -> set v_result=left(v_result,char_length(v_result)-1); -> return v_result; -> end; -> // Query OK, 0 rows affected (0.02 sec)mysql> select f_test_new('12.23.25.6'); -> // +--------------------------+ | f_test_new('12.23.25.6') | +--------------------------+ | 012.023.025.006 | +--------------------------+ 1 row in set (0.00 sec)mysql>
Select array_to_string(array[lpad(ip[1],3,'0'),lpad(ip[2],3,'0'),lpad(ip[3],3,'0'),lpad(ip[4],3,'0')],'.') From (Select string_to_array('12.23.25.6','.') As ip) as ips
mysql> create function f_test_new(i_str varchar(500))
-> returns varchar(1000)
-> begin
-> declare v_result varchar(1000);
-> declare v_temp_str varchar(50);
-> set v_result='';
-> set i_str = concat(i_str,'.');
-> while instr(i_str,'.')>0 do
-> set v_temp_str = left(i_str,instr(i_str,'.')-1);
-> set v_temp_str = lpad(v_temp_str,3,'0');
-> set v_result = concat(v_result,v_temp_str,'.');
-> set i_str = right(i_str,char_length(i_str)-instr(i_str,'.'));
-> end while;
-> set v_result=left(v_result,char_length(v_result)-1);
-> return v_result;
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)mysql> select f_test_new('12.23.25.6');
-> //
+--------------------------+
| f_test_new('12.23.25.6') |
+--------------------------+
| 012.023.025.006 |
+--------------------------+
1 row in set (0.00 sec)mysql>