学步园

drop function if exists drop_shengmu;
delimiter $
create function drop_shengmu(name varchar(255) charset gbk)
returns varchar(255) charset gbk
begin
		declare result varchar(255) charset gbk default '';
		if (name!='ji'&&name!='qi'&&name!='xi'&&name!='yi'&&name!='er'&&name!='zhi'&&name!='chi'&&name!='shi'
				&&name!='zi'&&name!='ci'&&name!='si'&&name!='an'&&name!='ai'&&name!='e'&&name!='en') then
				if (instr(name,'zh')>0||instr(name,'ch')>0||instr(name,'sh')>0)  then
					 set	result = replace(replace(replace(name,'sh',''),'ch',''),'zh','');
				else
					if (left(name,1) = 'b') then
							set result = trim('b' from name);
					end if;
					if left(name,1) = 'p' then
							set result = trim('p' from name);
					end if;
					if left(name,1) = 'm' then
							set result = trim('m' from name);
					end if;
					if left(name,1) = 'f' then
							set result = trim('f' from name);
					end if;
					if left(name,1) = 'd' then
							set result = trim('d' from name);
					end if;
					if left(name,1) = 't' then
							set result = trim('t' from name);
					end if;
					if left(name,1) = 'n' then
							set result= substring(name,2);
					end if;
					if left(name,1) = 'l' then
							set result = trim('l' from name);
					end if;
					if left(name,1) = 'g' then
							set result = substring(name,2);
					end if;
					if left(name,1) = 'k' then
							set result= trim('k' from name);
					end if;
					if left(name,1) = 'h' then
							set result = trim('h' from name);
					end if;
					if left(name,1) = 'j' then
							set result = trim('j' from name);
					end if;
					if left(name,1) = 'q' then
							set result = trim('q' from name);
					end if;
					if left(name,1) = 'x' then
							set result = trim('x' from name);
					end if;
					if left(name,1) = 'r' then
							set result = trim('r' from name);
					end if;
					if left(name,1) = 'z' then
							set result = trim('z' from name);
					end if;
					if left(name,1) = 'c' then
							set result = trim('c' from name);
					end if;
					if left(name,1) = 's' then
							set result = trim('s' from name);
					end if;
					if left(name,1) = 'y' then
							set result = trim('y' from name);
					end if;
					if left(name,1) = 'w' then
							set result = trim('w' from name);
					end if;

				end if;
		else
			set result = name;
		end if;
	return result;
end;
$
delimiter ;

补充：

①去掉第一个字符

select substr('nong',2);

ong

②获取一句话中最后一个汉字

select substr(trim('半生已分孤眠过，山枕檀痕涴。'),-2,1);

涴

获取一句话中最后一个字符

select substr('半生已分孤眠过，山枕檀痕涴。',-1);

。

③一次替换到多个字符

select '半生已分孤眠过，山枕檀痕涴。',replace(replace('半生已分孤眠过，山枕檀痕涴。','山',''),'已','');

半生已分孤眠过，山枕檀痕涴。
半生分孤眠过，枕檀痕涴。