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

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

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'

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

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