How to count dot from a row in MYSQL

Question

select userid from user;

+---------+
| userid  |
+---------+
| .1.     |
| .1.1.   |
| .1.2.   |
| .1.2.1. |
+---------+

I want the result like this :

+---------+-------+
| userid  | count |
+---------+-------+
| .1.     | 2     |
| .1.1.   | 3     |
| .1.2.   | 3     |
| .1.2.1. | 4     |
+---------+-------+

The count field is about how many the dots are in a row.

What is the sql for that? Thank you

Does this answer your question? [Count the number of occurrences of a string in a VARCHAR field?](https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field) — Markus Zeller, Dec 06 '19 at 08:39

Caius Jard · Answer 1 · 2019-12-06T08:46:32.050

Do the length of userid, minus the length of replacing userid's dots with nothing

SELECT
  userid, 
  LENGTH(userid) - LENGTH(REPLACE(userid, '.', '')) as count 
FROM user

IN GENERAL for this technique we should carefully consider whether to use LENGTH or CHAR_LENGTH, the first one counts the number of bytes that make up the string, the second the number of chars. In this case it won't matter but it could in future if you use this technique on Unicode strings where the characters are represented by more than one byte

score 2 · Answer 2 · answered Dec 06 '19 at 08:40

2

You coudl check for the length before and after repalce the dot

  select  userid, length(userid) - length(replace(userid,'.',''))
  from my_table

answered Dec 06 '19 at 08:40

ScaisEdge

131,976
10
91
107

Tim Biegeleisen · Answer 3 · 2019-12-06T08:45:16.510

1

One straightforward way is to use replacements to count the number of dots:

SELECT
    userid,
    LENGTH(userid) - LENGTH(REPLACE(userid, '.', '')) AS count
FROM yourTable;

Demo

edited Dec 06 '19 at 08:45

answered Dec 06 '19 at 08:39

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 4 · edited Dec 06 '19 at 09:02

1

The "." is a character which can be counted as any other character like "A", "7", " ". So you need to tell MySQL query which character name you want to count from a string.

See the code below:

SELECT 
      (CHAR_LENGTH(userid) - CHAR_LENGTH(REPLACE(userid, '.', ''))) AS count
FROM table_name;

For more details refer the link below Get count of zeros in an integer using MySQL

edited Dec 06 '19 at 09:02

Nikolai Shevchenko

7,083
8
33
42

answered Dec 06 '19 at 08:48

Mahadi Matsawily

19
1
3

How to count dot from a row in MYSQL

4 Answers4

Demo