SQL - How To Count Certain Char in Row-CodePudding

I have a column called colors that lists some colors, separated by semicolon.

I want to include in my select statement, a way to create a column that counts the semicolon for each respective row.

Here is sample output

Assume the only column we have to work with is Colors from table called SampleTable

CodePudding user response：

try this query

select Colors, LENGTH(Colors)- LENGTH(REPLACE(Colors, ';', '')) as CountSemiColon  from table_name

CodePudding user response：

You can count only sign ; with this example, here find count sign l

select host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt from user;
 ----------- -------- 
| host      | cnt    |
 ----------- -------- 
| 127.0.0.1 | 0.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| localhost | 2.0000 |
| localhost | 2.0000 |
 ----------- -------- 
5 rows in set (0.00 sec)

Example find in this answer

CodePudding user response：

Try this :

SELECT colors,
   ROUND ((LENGTH(colors) - LENGTH(REPLACE(colors, ';', ''))) / LENGTH(';')) 
   AS char_count 
   FROM table_name

CodePudding user response：

You can use IF to handle the null (count = 0) case as well.

select Colors, IF (Colors IS NULL, 0, LENGTH(Colors) - LENGTH(REPLACE(Colors, ';', ''))) as CountSemiColon from SampleTable;