insert into chongfu (names) values ("张三张三");
insert into chongfu (names) values ("李四李四");
insert into chongfu (names) values ("王五王五");
insert into chongfu (names) values ("张三一张三一");
insert into chongfu (names) values ("李四一李四一");
insert into chongfu (names) values ("麻子,赵柳麻子,赵柳");
insert into chongfu (names) values ("如梦,赵柳如梦,赵柳");
insert into chongfu (names) values ("何朝霞,柳如梦何朝霞,柳如梦");
-- 从第几个开始截取,截几个
-- 类型一 1,2 3,2 例如:张三张三 截取后是-- 张三 张三
-- 类型二 1,3 4,3 例如:张三一张三一 截取后是-- 张三一 张三一
-- 类型三 1,4 5,4 例如:张三一二张三一二 截取后是-- 张三一二 张三一二
-- 最长101字节
select passenger_names from return_orders_finish where length(passenger_names) = 101;
create procedure test(in startNo int,in start2No int,in len int)
BEGIN
declare idtemp int(11); #申明ID变量
declare done boolean DEFAULT true; #申明循环变量
declare mysort int(11); #索引
#查询ID,并将news_detail_id设置到游标变量中
DECLARE programName VARCHAR(100);
declare cur CURSOR for select u.id from chongfu u where
(select substring
(names, startNo,len) from chongfu c
where c.id =
u.id ) = (select substring(names,
start2No,len) from chongfu f where f.id = u.id) ;
open cur;
while done do
fetch cur into idtemp;
set programName=(select (select substring(names, startNo,len) from
chongfu f where f.id = u.id) as names from
chongfu u where
(select
substring(names, startNo,len) from chongfu c
where c.id =
u.id ) = (select substring(
names, start2No,len) from chongfu f where f.id = u.id
and f.id = idtemp) );
#更新语句
update chongfu set names=programName where id=idtemp;
set mysort=mysort+1;
end while;
close cur;
END;
-- 参数1是开始截取位置,参数2是第二段截取的位置,参数3是截取的长度
call test(1,8,7); #调用存储过程
drop procedure test ; #删除存储过程