Is it possible in sql to group by fields matching some pattern?

Question

Is it possible in SQL to do grouping by LIKE patterns? I would like to achieve something like this:

id|name  
1 | Mike  
2 | Bob  
3 | Bill  
4 | Alice

and then doing query like: SELECT name from users group by _pattern_
For example I would like to get groups by matching patterns 'B*', '*l*' and 'Mike' would give the output:

B*  | Bob
    | Bill
*l* | Bill
    | Alice
Mike| Mike

Answer 1

Select 'B*' as Mask, Name
from Table
WHERE Name like 'B%'
UNION ALL
Select '*l*' as Mask, Name
from Table
WHERE Name like '%l%'
UNION ALL
Select 'Mike' as Mask, Name
from Table
WHERE Name like 'Mike'

Answer 2

If you want the same record to appear multiple times according to the pattern it matches, you should use multiple SELECT statements with the relevant filters and UNION them together..

Answer 3

You can query against the patterns in a set structure then GROUP BY or DISTINCT to remove dups, below is a way with an MSSQL CTE (temp table/table var would work also);

with match (pattern) as (
          select 'B%'
    union select '%l%' 
    union select 'Mike' 
)
select
    pattern,
    name
from TABLE, match where TABLE.name like match.pattern
group by pattern, name

==
%l%     Alice
%l%     Bill
B%      Bill
B%      Bob
Mike    Mike