mysql 查找一个列中，一个字符出现的次数

首先大家会想到写一个函数来解决

CREATE  FUNCTION sqe.GetCharIndexNum (findstring VARCHAR(3000),string1 VARCHAR(3000))
RETURNS INT
AS
BEGIN
   DECLARE location INT ,  
           num INT  
    
   SET num =0
   SET location = charindex ( findstring, string1)
   WHILE location >0  
     BEGIN
      SET num =num +1
      SET string1 =SUBSTRING(string1,location+1,len(string1))
      SET location = charindex (findstring,string1)
    END
RETURN num
END

第2种：

length(列)-length(replace(列,'^','')) 

SELECT dt.`username`, LENGTH(dt.ker) - LENGTH(REPLACE (dt.ker, ',', ''))  ,dt.ker FROM ( 
SELECT c.`username`,b.`MENUNAME` ,a.moduleid
,SUBSTR(a.message, INSTR(a.message,'维度'), INSTR(a.message,'指标选择')-INSTR(a.message,'维度')) AS dim
,SUBSTR(  SUBSTR(a.message, INSTR(a.message,'指标选择') ) ,1,INSTR( SUBSTR(a.message, INSTR(a.message,'指标选择') ),'维度')-1 )    AS ker
, a.message
FROM sys_eventlog a LEFT JOIN sys_menu_bs b
ON a.`MODULEID`=b.`MENUID`
LEFT JOIN sys_users c
ON a.userid=c.`userid`
WHERE logtime  >= DATE_FORMAT('2017-05-05',   '%Y-%m-%d %H:%i:%S')
AND logtime <DATE_FORMAT('2018-02-24',   '%Y-%m-%d %H:%i:%S')   
 AND b.MENUNAME LIKE '%数据业务%'  
 AND  a.message LIKE '%用户%' 
 AND c.`username` NOT IN ('微微','loney','zhangak','良海','tianyou','hujb','杨','admin'
     ,'liuhb','renli','shiy','yinhy','yucheng','zhangak1','黎平')
 
 ) dt
 WHERE dt.ker  IS NOT NULL AND dt.ker !=''
ORDER BY 2 DESC