mysql中处理指定分隔符分隔的字符串,取范围例子

函数主要实现指定标点符号分隔的各数据间处理.适用于类似打页面面范围等数据的精确提取.
如打印范围 ' 1-3 ' , 8 , ' 9-12 ' 之类的情况.示例中对处理手机号段进行了处理,第一行中的1300,1301等为手机前四位号码.
其余各行为手机子号段范围.以 ' , ' 及 ' - ' 符号分隔.为精确取到手机整个号段.需要将手机子号段范围精确到每一个值,如 ' 970-972,980 '
需要处理为 ' 1300970 ' , ' 1300971 ' , ' 1300972 ' , ' 1300980 '
1 .建表
CREATE   TABLE  mytable (
  id  int ( 11 )  NOT   NULL  auto_increment,
  province  varchar ( 50 )  default   NULL ,
  citys  varchar ( 50 )  default   NULL ,
  areacode  varchar ( 10 )  default   NULL ,
  areacode0  varchar ( 2000 )  default   NULL ,
  areacode1  varchar ( 2000 )  default   NULL ,
  areacode2  varchar ( 2000 )  default   NULL ,
  areacode3  varchar ( 2000 )  default   NULL ,
  areacode4  varchar ( 2000 )  default   NULL ,
  areacode5  varchar ( 2000 )  default   NULL ,
  areacode6  varchar ( 2000 )  default   NULL ,
  areacode7  varchar ( 2000 )  default   NULL ,
  areacode8  varchar ( 2000 )  default   NULL ,
  areacode9  varchar ( 2000 )  default   NULL ,
   PRIMARY   KEY   (id)
) ENGINE = MyISAM 
2 .插入准备数据
insert   into  mytable
(province,citys,areacode,areacode0,areacode1,areacode2,areacode3,areacode4,areacode5,areacode6,areacode7,areacode8,areacode9)
select   ' 省份 ' , ' 所辖城市 ' , ' 长途区号 ' , ' 1300 ' , ' 1301 ' , ' 1302 ' , ' 1303 ' , ' 1304 ' , ' 1305 ' , ' 1306 ' , ' 1307 ' , ' 1308 ' , ' 1309 '   union   all
select   ' 黑龙江 ' , ' 哈尔滨 ' , ' 451 ' , ' 970-972,980,984-987,999 ' , ' 900-901,970-972 ' , ' 970-972,980,984-987,999 ' , ' 000-009,996-999 ' , ' 510-519 ' , ' 426-429,900-903 ' , ' 970-972,986-989 ' , ' 450-459 ' , ' 970-972,998-999 ' , ' 143-145,170-172,186-189 '   union   all
select   ' 黑龙江 ' , ' 齐齐哈尔 ' , ' 452 ' , ' 973-975 ' , ' 903,909,973,978 ' , ' 973-975 ' , ' 974 ' , ' 520-524,527 ' , ' 424,430-431 ' , ' 963,968,973,997-999 ' , ' 960-969 ' , ' 973-975 ' , ''
3 .建立函数,对数据进行处理
DELIMITER $$

DROP   FUNCTION   IF   EXISTS  `radius`.`myareacode`$$

CREATE  DEFINER = `yp`@` % `  FUNCTION  `myareacode`(myid  int , str   varCHAR ( 2000 ))  RETURNS   varchar ( 2000 ) CHARSET latin1
    READS SQL DATA
BEGIN
         DECLARE  tmpid  int ;
         DECLARE  tmpac  varchar ( 10 );
         DECLARE  tmpposin,tmppos  int ;
         DECLARE  tmpbg,tmped  int ;
         DECLARE  tmpsplit  varchar ( 2000 );
         DECLARE  tmpstr  varchar ( 2000 );
         DECLARE  tmpmid  varchar ( 2000 );
         DECLARE  tmpResult  varchar ( 2000 );
         SET  tmpmid = '' ;
         SET  tmpstr = '' ;
         SET  tmpResult = '' ;
         --  get areacode 取得最上面一行中的手机前四位号码,此示例中只处理areacode0字段
         select   max (id)  into  tmpid  from  mytable  where  id  < myid  and  province = ' 省份 '  limit  1 ;
         select  areacode0  into  tmpac  from  mytable  where  id = tmpid;
             WHILE  INSTR( str , ' , ' ) > 0  DO
                 --  如果有','符号分隔,处理分隔符号内的每一字段
                 SET  tmpmid = '' ;
                 SET  tmpstr = SUBSTRING ( str , 1 ,INSTR( str , ' , ' ) - 1 );
                 SET  tmppos  =  INSTR(tmpstr,  ' - ' );
                 IF  tmppos > 0   THEN
                     --  如果有'-'符号分隔时,
                     SET  tmpbg = SUBSTRING (tmpstr, 1 ,tmppos - 1 );
                     SET  tmped = SUBSTRING (tmpstr,tmppos + 1 );
                     WHILE  tmpbg  <=  tmped DO
                         --  循环处理'-'符号分隔的内容
                         SET  tmpmid = CONCAT(tmpmid,tmpac,tmpbg, ' , ' );
                         SET  tmpbg  =  tmpbg  +   1 ;
                     END   WHILE ;
                     -- 去右侧多余的括号
                     SET  tmpmid  =   LEFT (tmpmid,length(tmpmid) - 1 );
                 ELSE
                     --  无'-'符号分隔时
                     SET  tmpmid = CONCAT(tmpac,tmpstr);
                 END   IF ;
                 SET  tmpResult = CONCAT(tmpResult,tmpmid, ' , ' );
                 SET   str = SUBSTRING ( str ,INSTR( str , ' , ' ) + 1 );
             END   WHILE ;
             SET  tmpmid = '' ;
             SET  tmpstr = str ;
             SET  tmppos  =  INSTR(tmpstr,  ' - ' );
             IF  tmppos > 0   THEN
                 SET  tmpbg = SUBSTRING (tmpstr, 1 ,tmppos - 1 );
                 SET  tmped = SUBSTRING (tmpstr,tmppos + 1 );
                 WHILE  tmpbg  <=  tmped DO
                     SET  tmpmid = CONCAT(tmpmid,tmpac,tmpbg, ' , ' );
                     SET  tmpbg  =  tmpbg  +   1 ;
                 END   WHILE ;
                 SET  tmpmid  =   LEFT (tmpmid,length(tmpmid) - 1 );
             ELSE
                 SET  tmpmid = CONCAT(tmpac,tmpstr);
             END   IF ;
             SET  tmpResult = CONCAT(tmpResult,tmpmid, ' , ' );
        
             Return  tmpResult;
         END $$

DELIMITER ;
4 .执行函数返回结果集
select  myareacode(id,areacode0)  from  mytable
结果集
myareacode(id,areacode0)
" 15501300 ,"
" 1300970 , 1300971 , 1300972 , 1300980 , 1300984 , 1300985 , 1300986 , 1300987 , 1300999 ,"
" 1300973 , 1300974 , 1300975 ,"
" 1300979 , 1300988 , 1300989 ,"
" 1300995 ,"
" 1300996 ,"
" 1300976 , 1300977 , 1300978 ,"
" 1300997 ,"
" 1300998 ,"
" 1300991 , 1300992 ,"
" 1300993 , 1300994 ,"
" 1300 ,"
" 1300 ,"
" 1300981 , 1300982 , 1300983 , 1300990 ,"
-- 最终结果还可以通过存储过程,将字符串按','分隔,形成纵列,返回结果集.