T-sql - Group By With Like - Is This Possible?

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-Fal

Solution 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 statementSELECTCASEWHEN column_x LIKE'%Fall[_]2009'THEN'Fall 2009'WHEN column_x LIKE'%Spring[_]2009'THEN'Spring 2009'ENDAS group_by_value
, COUNT(*) AS group_by_count
FROM Table1 a
GROUPBYCASEWHEN 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 dashSELECT 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'') AS group_by_value
, COUNT(*) as group_by_count
FROM Table1 a
GROUPBY 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'')

or

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

Solution 2:

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

SELECT x.term,
         COUNT(*)
    FROM (SELECTCASEWHEN CHARINDEX('Fall_2009', t.column) >0THENSUBSTRING(t.column, CHARINDEX('Fall_2009', t.column), LEN(t.column))
                   WHEN CHARINDEX('Spring_2009', t.column) >0THENSUBSTRING(t.column, CHARINDEX('Spring_2009', t.column), LEN(t.column))
                   ELSENULLENDas TERM
            FROMTABLE t) x
GROUPBY x.term

Solution 3:

LIKE does not make sense in your context, as it either matches or it does not, but it does not establish groups. You will have to use string functions that parse the column values into what makes sense for your data.

Solution 4:

I dont believe so, LIKE is effectively a binary state - something is LIKE or NOT LIKE, there are not logical degrees of 'likeness' that could be grouped together. Then again, I could be off my rocker.

If what you really want is to express filtering over your grouped data take a look at the HAVING clause.

http://msdn.microsoft.com/en-us/library/ms180199.aspx

Solution 5:

If your courses always take five letters, you can keep it really simple:

SELECT substring(column_x,5,100), count(*)
FROM YourTable
GROUP BY substring(column_x,5,100)

Otherwise, check Peters or Rexem's answer.