distinct and sum if like

Question

I have a table as the following

name
-----------
1@apple@1
2@apple@2
3@apple@4
4@box@4
5@box@5

and I want to get the result as:

name
--------------
apple 3
box   2

Thanks in advance for your help

Answer 1

If your data does not contain any full stops (or periods depending on your vernacular), and the length of your string is less than 128 characters, then you can use PARSENAME to effectively split your string into parts, and extract the 2nd part:

DECLARE @T TABLE (Val VARCHAR(20));
INSERT @T (Val)
VALUES ('1@apple@1'), ('2@apple@2'), ('3@apple@4'), 
        ('4@box@4'), ('5@box@5');

SELECT  Val = PARSENAME(REPLACE(t.Val, '@', '.'), 2),
        [Count] = COUNT(*)
FROM    @T AS t
GROUP BY PARSENAME(REPLACE(t.Val, '@', '.'), 2);

Otherwise you will need to use CHARINDEX to find the first and last occurrence of @ within your string (REVERSE is also needed to get the last position), then use SUBSTRING to extract the text between these positions:

DECLARE @T TABLE (Val VARCHAR(20));
INSERT @T (Val)
VALUES ('1@apple@1'), ('2@apple@2'), ('3@apple@4'), 
        ('4@box@4'), ('5@box@5');

SELECT  Val = SUBSTRING(t.Val, x.FirstPosition + 1, x.LastPosition - x.FirstPosition),
        [Count] = COUNT(*)
FROM    @T AS t
        CROSS APPLY 
        (   SELECT  CHARINDEX('@', t.Val) ,
                    LEN(t.Val) - CHARINDEX('@', REVERSE(t.Val))
        ) AS x (FirstPosition, LastPosition)
GROUP BY SUBSTRING(t.Val, x.FirstPosition + 1, x.LastPosition - x.FirstPosition);