The indexOf-like method LOCATE () and indexoflocate in mysql

The indexOf-like method LOCATE () and indexoflocate in mysql

 

LOCATE (substr, str), LOCATE (substr, str, pos)

The first syntax returns the position of substr In the first occurrence of the string str.

The second syntax returns the position of the substring substr in the string str, the first occurrence from the pos. If substr is not in str, the return value is 0.

 

Question: there are multiple subjects. One subject has multiple answers, and each question has four answers (ABCD). The number of ABCD choices is counted.

SELECT
Sum (
CASE
WHEN (
LOCATE ('A', option name)> 0
) THEN 1 ELSE
0
END
) As,
Sum (
CASE
WHEN (
LOCATE ('B', option name)> 0
) THEN
1
ELSE
0
END
) As B,
Sum (
CASE
WHEN (
LOCATE ('C', option name)> 0
) THEN
1
ELSE
0
END
) As C,
Sum (
CASE
WHEN (
LOCATE ('D', option name)> 0
) THEN
1
ELSE
0
END
) As D
FROM
Table Name