[code=sql]SET @str:='123P22P33P123'; SET @substr:='P'; SELECT IF(LOCATE(SUBSTRING_INDEX(@str,@substr,-1),@str)+LENGTH(SUBSTRING_INDEX(@str,@substr,-1))-1=LENGTH(@str),@str,REVERSE(SUBSTR(REVERSE(@str),LENGTH(SUBSTRING_INDEX(@str,@substr,-1))+1+LENGTH(@substr))));code]
SET @str:='123P22P33P123'; SET @substr:='P'; SELECT IF(LOCATE(SUBSTRING_INDEX(@str,@substr,-1),@str)+LENGTH(SUBSTRING_INDEX(@str,@substr,-1))-1=LENGTH(@str),@str,REVERSE(SUBSTR(REVERSE(@str),LENGTH(SUBSTRING_INDEX(@str,@substr,-1))+1+LENGTH(@substr))));
substring_index(str,delim,count)
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
-> ‘www.mysql’
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
-> ‘mysql.com’
UPDATE TABLE SET col=SUBSTRING_INDEX(col,"P",1)
数据内容中P的个数是不固定的,有的是 123P22P33P123 有的是123P123 我只想把最后重复的那个尾巴删掉。
SET @substr:='P';
SELECT IF(LOCATE(SUBSTRING_INDEX(@str,@substr,-1),@str)+LENGTH(SUBSTRING_INDEX(@str,@substr,-1))-1=LENGTH(@str),@str,REVERSE(SUBSTR(REVERSE(@str),LENGTH(SUBSTRING_INDEX(@str,@substr,-1))+1+LENGTH(@substr))));code]
SET @substr:='P';
SELECT IF(LOCATE(SUBSTRING_INDEX(@str,@substr,-1),@str)+LENGTH(SUBSTRING_INDEX(@str,@substr,-1))-1=LENGTH(@str),@str,REVERSE(SUBSTR(REVERSE(@str),LENGTH(SUBSTRING_INDEX(@str,@substr,-1))+1+LENGTH(@substr))));