CODEWITHSUNDEEP
sqlsql-like
wlk
wlk

Reputation: 5785

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

Upvotes: 2

Views: 4202

Answers (3)

Alex K.
Alex K.

Reputation: 175816

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

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196002

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

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25380

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'

Upvotes: 2

Related Questions