mysql自定义函数（貌似处理页码）

T1表内容

name

-----------------------------

1-3,8,10-12
4,5,6
1,3-5,14
4
1-7,9

要转换为

 

--------------------------------------------------------------------------------

CREATE FUNCTION myFunction
(in_string      VARCHAR(255))
RETURNS VARCHAR(255)
begin
DECLARE l_new_string VARCHAR(255);
DECLARE part_string VARCHAR(255);
DECLARE num1 int;
DECLARE num2 int;
set l_new_string='';
while INSTR(in_string,',')>0 do
SET part_string=left(in_string,INSTR(in_string,',')-1);
IF (INSTR(part_string,'-'))>0 THEN
SET num1=left(part_string,INSTR(part_string,'-')-1);
SET num2=right(part_string,length(part_string)-INSTR(part_string,'-'));
 while num1<=num2 do
 set l_new_string=concat(l_new_string,' ',num1);
 set num1=num1+1;
 end while;
ELSE
set l_new_string=concat(l_new_string,' ',part_string);
end if;
set in_string=right(in_string,length(in_string)-INSTR(in_string,','));
end while;
IF (INSTR(in_string,'-'))>0 THEN
SET num1=left(in_string,INSTR(in_string,'-')-1);
SET num2=right(in_string,length(in_string)-INSTR(in_string,'-'));
 while num1<=num2 do
 set l_new_string=concat(l_new_string,' ',num1);
 set num1=num1+1;
 end while;
ELSE
set l_new_string=concat(l_new_string,' ',in_string);
end if;
RETURN(ltrim(l_new_string));
end

----------------------------------------------------------------------------------

 

结果正常

-------------

恶心的开发提出需求，不知道干啥用的，鄙视一下

 

---------------------------------------------------------------

一个很简单的 MySQL 函数定义，学习语法

01

mysql>

02	mysql> delimiter $$

03

mysql>

04	mysql> CREATEFUNCTION myFunction

05	->    (in_string     VARCHAR(255),

06	->     in_find_str   VARCHAR(20),

07	->     in_repl_str   VARCHAR(20))

08

->

09	->   RETURNS VARCHAR(255)

10

-> BEGIN

11	->   DECLARE l_new_string VARCHAR(255);

12	->   DECLARE l_find_pos   INT;

13

->

14	->   SET l_find_pos=INSTR(in_string,in_find_str);

15

->

16	->   IF (l_find_pos>0)THEN

17	->    SET l_new_string=INSERT(in_string,l_find_pos,LENGTH(in_find_str),in_repl_str);

18

->   ELSE

19	->    SET l_new_string=in_string;

20	->   END IF;

21	->   RETURN(l_new_string);

22

->

23

-> END$$

24	Query OK, 0 rowsaffected (0.00 sec)

25

26

mysql>

27	mysql> delimiter ;

28	mysql> selectmyFunction('ABC','A','Z');

29	+---------------------------+

30	| myFunction('ABC','A','Z') |

31	+---------------------------+

32	| ZBC                       |

33	+---------------------------+

34	1 row inset (0.00 sec)

35

36	mysql> dropfunction myFunction;

37	Query OK, 0 rowsaffected (0.00 sec)

38

39

mysql>

40

mysql>

delimiter 标志开始，delimiter再标志函数结束，执行