Table out results by identifying patterns in a string field

[sparachi]

I would like to get results by identifying a patterns with in string filed based on the string match/pattern/occurrence.
Ex::
File_name filed has a collection of string values like ABCD , ABCDG, ABCGRC, ABCD21,ABCDXYZ ,INDIAUS,INDIABritain,INDIAAus....
I need to get below output based on count of occurrences of word in each value of File_name filed.
Patters::
ABCD
INDIA
ABC and so on..

Hint: ABCD has occurred 4 times in ABCD,ABCDG,ABCD21,ABCDXYZ and INDIA 3 times and so on.

[sparachi]

Thanks for your response .. your solution works part of my requirement but we are not sure that in only first 4 characters we find the pattern .. string may also be of 22na9INDI, USAABCD, 123INDIBritain..like that ..