SQL: counting set elements after truncation

select THING from FOLDER where THING like '%-%' and ...

which returns things like:

abc097-01
def982-02
ghi13344-01
ghi13344-02

Now I need to change this query so that I can count distinct names, after truncating the part after the '-'.

So for the example above, I'd get 3 as a resu开发者_运维问答lt ("abc097","def982" and "ghi13344").

How can I write that in SQL?

---

Declare @tmp table (field nvarchar(100))
insert into @tmp values ('abc097-01')
insert into @tmp values ('def982-02')
insert into @tmp values ('ghi13344-01')
insert into @tmp values ('ghi13344-02')

select COUNT(*),SUBSTRING(field,0,CHARINDEX('-',field))
from @tmp
group by SUBSTRING(field,0,CHARINDEX('-',field))


select distinct(SUBSTRING(field,0,CHARINDEX('-',field)))
from @tmp -- to get distinct records.

---

You can use CHARINDEX or PATINDEX functions to find the location of the '-' and then use the LEFT or SUBSTRING functions:

select COUNT(DISTINCT SUBSTRING(THING, 1, PATINDEX('%-%', THING) - 1))
from FOLDER
where THING like '%-%'