T-SQL - GROUP BY with LIKE - is this possible?

Question

Is there a way to include a LIKE expression in a GROUP BY query? For example:

SELECT Count(*) 
FROM tblWhatever
GROUP BY column_x [LIKE %Fall-2009%]

column_x:

--------
BIOL-Fall_2009
HIST Fall_2009
BIOL Spring_2009

Result:

------
Fall_2009   2
Spring_2009 1

Answer 1

You need an expression that returns "Fall_2009" or "Spring_2009", and then group on that expression. eg:

-- identify each pattern individually w/ a case statement
SELECT
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END AS group_by_value
, COUNT(*) AS group_by_count
FROM Table1 a
GROUP BY 
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END

or

-- strip all characters up to the first space or dash
SELECT 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'') AS group_by_value
, COUNT(*) as group_by_count
FROM Table1 a
GROUP BY 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'')

or

-- join to a (pseudo) table of pattern masks
SELECT b.Label, COUNT(*)
FROM Table1 a
JOIN (
  SELECT '%Fall[_]2009'  , 'Fall, 2009' UNION ALL
  SELECT '%Spring[_]2009', 'Spring, 2009'
  ) b (Mask, Label) ON a.column_x LIKE b.Mask
GROUP BY b.Label

Answer 2

No, the LIKE function is not supported in the GROUP BY clause. You'd need to use:

  SELECT x.term,
         COUNT(*)
    FROM (SELECT CASE
                   WHEN CHARINDEX('Fall_2009', t.column) > 0 THEN
                     SUBSTRING(t.column, CHARINDEX('Fall_2009', t.column), LEN(t.column))
                   WHEN CHARINDEX('Spring_2009', t.column) > 0 THEN
                     SUBSTRING(t.column, CHARINDEX('Spring_2009', t.column), LEN(t.column))
                   ELSE
                     NULL
                 END as TERM
            FROM TABLE t) x
GROUP BY x.term