group sql results by substring

Question

I have a table with some records where a column represents the group of every record:

id  | group
1     abc_xxx
2     abc_yyy
3     def_ooo
4     def_ppp
5     ghi_jjj
6     ghi_kkk

When I do a query and group the rows by the group column, I get

abc_xxx
abc_yyy
def_ooo
def_ppp
ghi_jjj
ghi_kkk

However it's not the correct output that I need, I need to get instead something like that:

abc
def
ghi

Because it represents the real classification of the records under the group column. So I'm looking for a method in SQL Server 2005 with which I can get that.

Answer 1

Use SUBSTRING or LEFT (which is simpler but non-standard):

SELECT
    SUBSTRING([group], 1, 3) AS [group]
FROM table
GROUP BY 
    SUBSTRING([group], 1, 3)

Or,

SELECT
    LEFT([group], 3) AS [group]
FROM table
GROUP BY 
    LEFT([group], 3)

When grouping it's usually to aggregate something, so I'm guessing you're looking for something like:

SELECT
    SUBSTRING([group], 1, 3) AS [group],
    COUNT(*) AS [count]
FROM table
GROUP BY 
    SUBSTRING([group], 1, 3)

Answer 2

SELECT
  t.id,
  t.groupcode
FROM
  (SELECT id, group, left(group,charindex('_',group)-1) as groupcode FROM table) t
GROUP BY
  groupcode