"group by" by excluding prefix

Suppose there is a table containing these recodes.

country
---
CON_CHN
CON_JAP
JAP
CON_CHN

When I use the following sql:
select country, count(*) as num from table group by country

the normal result will be:
country num
-----
CON_CHN2
CON_JAP 1
JAP1

However, my desired result is as follows:
country num
------
CON_CHN2
CON_JAP 2

How can I re-write my SQL? Or any other methods to do that?Is the length of the string (prefix or sufix) fixed? Can the string be
something like CON_CANA ? Also, it would help if you provided sample
scripts...

MC

"littlebeam" <cyrus.lan@.gmail.com> wrote in message
news:1144831660.591396.269760@.e56g2000cwe.googlegr oups.com...
> Suppose there is a table containing these recodes.
> country
> ---
> CON_CHN
> CON_JAP
> JAP
> CON_CHN
> When I use the following sql:
> select country, count(*) as num from table group by country
> the normal result will be:
> country num
> -----
> CON_CHN 2
> CON_JAP 1
> JAP 1
> However, my desired result is as follows:
> country num
> ------
> CON_CHN 2
> CON_JAP 2
> How can I re-write my SQL? Or any other methods to do that?|||If your prefix is always "CON_" then this should work

SELECT Replace(country, 'CON_',''), COUNT(Replace(country, 'CON_',''))
from myTable
Group by Replace(country, 'CON_','')

Markus