Count substring_index

Question

I need to know how substring_index can only return all rows matching exactly the number of delimiters. In this case .

For example, this query:

  SELECT SUBSTRING_INDEX(ABC, '.', 4) FROM xxx 

should be output only if the string exactly matches this (with 4 words):

 aaa.bbb.ccc.ddd 

The problem is that: this line is also shown.

 aaa.bbb 

Answer 1

This will return everything where ABC has 3 . delimiters.

 select * from xxx where char_length(replace(ABC, '.', '')) + 3 = char_length(ABC) 

You will need to multiply 3 by the length of the separator if there is a multi-character string for the separator.