CODEWITHSUNDEEP
mysql
Eric
Eric

Reputation: 10658

select distinct substring values of a field and count the number of instances of a char in that selection

I'm trying to select distinct substring values of a field and count the number of instances of a char in that selection. I've found this wonderful post which answers half of it.

So, so far, i can count the instances of a char in my field, it works great. Now the even harder part, what if i select a piece of string using :

SELECT DISTINCT SUBSTRING_INDEX(my_field, '-', -1) AS chunk

In this case i'm only selecting the last part of the string (everything after the last'-'). How can i apply this formula to chunk (trying to count the number of instances of '_' in the new string ? :

(LENGTH(chunk) - LENGTH(REPLACE(chunk, '_', ''))) / LENGTH('_')

I know i shoud be using HAVING to make operation on chunk as it's not a real field, but how can i do something like :

SELECT DISTINCT SUBSTRING_INDEX(my_field, '-', -1) AS chunk, (LENGTH(chunk) - LENGTH(REPLACE(chunk, '_', ''))) / LENGTH('_') AS total FROM my_field HAVING total < 2

The problem here is that i can't use 'chunk' in the last part since it's not a field..

Upvotes: 1

Views: 1387

Answers (1)

fancyPants
fancyPants

Reputation: 51938

The problem here is that i can't use 'chunk' in the last part since it's not a field..

Replace 'chunk' in the last part with

SUBSTRING_INDEX(my_field, '-', -1)

Don't know what's the problem?

Upvotes: 3

Related Questions