How to convert this query to MySQL from MSSQL?-CodePudding

How to convert this query to MySQL from MSSQL?

Query:

SELECT count(id) AS counter, 
        SUBSTRING(user_name, CHARINDEX('@', user_name)   1, LEN(user_name)) AS domain, 
        comp_id 
FROM user_stat 
WHERE ok='y'  
GROUP BY SUBSTRING(user_name, CHARINDEX('@', user_name)   1, LEN(user_name)), 
        comp_id  
ORDER BY counter DESC

Mostly this part is what i care:

SUBSTRING(user_name, CHARINDEX('@', user_name)   1, LEN(user_name)) AS domain

I can not find solution for this. ..
Thanks in advance!

CodePudding user response：

SUBSTRING(user_name, LOCATE('@', user_name)   1) AS domain

CodePudding user response：

You only have to change LEN with LENGTH and CHARINDEX with LOCATE: https://dev.mysql.com/doc/refman/8.0/en/functions.html

SELECT count(id) AS counter, 
        SUBSTRING(user_name, LOCATE('@', user_name)   1, LENGTH(user_name)) AS domain, 
        comp_id 
FROM user_stat 
WHERE ok='y'  
GROUP BY SUBSTRING(user_name, LOCATE('@', user_name)   1, LENGTH(user_name)), 
        comp_id  
ORDER BY counter DESC