How to extract left most common characters in a string field in multiple rows?

Question

I have a table with some rows that have common groups:

Id    Name    Group
1     ABC1    G1
2     ABC2    G1
3     ABC3    G1
4     AXX3    G2

At some point I know the group I need to query (G1 for example). Then I need to query the left most common characters in the Name field among the rows filtered by their Group. So in this case, I'd get ABC.

Can this be performed in one query ? I need to do this in the simplest way possible.

Answer 1

You can do it by brute force:

select groupid,
       (case when min(left(name, 10)) = max(left(name, 10)) then left(name, 10)
             when min(left(name, 9)) = max(left(name, 9)) then left(name, 9)
             when min(left(name, 8)) = max(left(name, 8)) then left(name, 8)
             when min(left(name, 7)) = max(left(name, 7)) then left(name, 7)
             when min(left(name, 6)) = max(left(name, 6)) then left(name, 6)
             when min(left(name, 5)) = max(left(name, 5)) then left(name, 5)
             when min(left(name, 4)) = max(left(name, 4)) then left(name, 4)
             when min(left(name, 3)) = max(left(name, 3)) then left(name, 3)
             when min(left(name, 2)) = max(left(name, 2)) then left(name, 2)
             when min(left(name, 1)) = max(left(name, 1)) then left(name, 1)
        end) as CommonPrefix
from t
group by groupid;

If you don't like typing so much, you can also do:

select groupid,
       max(case when min(left(name, n.n)) = max(left(name, n.n)) then left(name, n.n) end)
from t cross join
     (select 1 as n union all selet 2 union all select 3 . . .
     ) n
group by groupid;

(Or use a where clause to get the information for one group.) For this example, just keep adding integers to the n subquery up to the length you want to test.