如何计算字符串特定位置中特定字符的出现次数

如何计算字符串特定位置中特定字符的出现次数 - MYSQL 5.5(How to Count occurrence of a specific character in a specific position of string - MYSQL 5.5)

我在列中有这种类型的数据：

!-------!---!------- !-------!-----!----- !-------!!---------- !-------!-----!----- !-------!-----!-----

我需要计算'！'的出现次数 - 在字符串的每个位置。

对于位置1 - 我应该得到5，位置2 - 0位置3 - 0位置4 - 0位置5 - 0位置6 - 0位置7 - 0位置8 - 0位置9 - 5依此类推等等。 有20个职位。 我想忽略' - '。

我试过使用locate：

select `color` AS `color`, locate('!', `Info`) AS `Position`, count(`Info`) AS `Count` from `CountReport` where (locate('!', `Info`) = 1) group by `color`

但如果'！' 每次不计算角色的其他实例时，它会显示在第一个位置。 我有一个每个职位的脚本。

对此有任何帮助将不胜感激。 非常感谢提前！

- H

I have this type of data in a column:

!-------!---!------- !-------!-----!----- !-------!!---------- !-------!-----!----- !-------!-----!-----

I need to count the occurrence of '!' - in EACH position of the string.

For position 1 - I should get a count of 5, position 2 - 0 position 3 - 0 position 4 - 0 position 5 - 0 position 6 - 0 position 7 - 0 position 8 - 0 position 9 - 5 So on and so on. There's 20 positions. I want to ignore the '-'.

I have tried to use locate:

select `color` AS `color`, locate('!', `Info`) AS `Position`, count(`Info`) AS `Count` from `CountReport` where (locate('!', `Info`) = 1) group by `color`

But if the '!' shows up in the first position every time it doesn't count the other instances of the character. I have a script for each position.

ANY help with this would be greatly appreciated. Thanks so much in advance!

~h

最满意答案

我不确定这是否是最有效的方法：

select count(case substring(s,1,1) when '!' then 1 else NULL end) as pos1, ... count(case substring(s,10,1) when '!' then 1 else NULL end) as pos10, ... count(case substring(s,20,1) when '!' then 1 else NULL end) as pos20 from test;

SQLFiddle

i'm not sure whether it's the most efficient way to do this:

select count(case substring(s,1,1) when '!' then 1 else NULL end) as pos1, ... count(case substring(s,10,1) when '!' then 1 else NULL end) as pos10, ... count(case substring(s,20,1) when '!' then 1 else NULL end) as pos20 from test;

SQLFiddle