SQL query to count all occurences that start with a substring

Suppose that I have the following 开发者_JAVA百科table:

ID:   STR:
01    abc
02    abcdef
03    abx
04    abxy
05    abxyz
06    abxyv

I need to use an SQL query that returns the ID column and the occurrences of the corresponding string as a prefix for string in other rows. E.g. The desired result for the table above:

ID:   OCC:
01    2
02    1
03    4
04    3
05    1
06    1

You could JOIN the table with itself and GROUP BY the ID to get you the result.

SELECT   t1.ID, COUNT(*)
FROM     ATable t1
         INNER JOIN ATable t2 ON t2.Str LIKE t1.Str + '%'
GROUP BY
         t1.ID

Some notes:

• You want to make sure you have an index on the Str column
• Depending on the amount of data, your DBMS might choke on the amount it has to handle. Worst case, you are asking for the SQR(#Rows) in your table.

Can can do that in SQL Server's T-SQL with the following code. Caution: I do not guarantee how this will perform though with a large dataset!

Declare @Table table
    (
      Id int,
      String varchar(10)
    )

Insert  Into @Table
        ( Id, String )
Values  ( 1, 'abc' ),
        ( 2, 'abcdef' ),
        ( 3, 'abx' ),
        ( 4, 'abxy' ),
        ( 5, 'abxyz' ),
        ( 6, 'abxyv' )

Select  t.Id,
        t.String
From    @Table as t
        Inner Join @Table as t2 On t2.String Like t.String + '%'
Order By t.Id

Select  t.Id,
        Count(*) As 'Count'
From    @Table as t
        Inner Join @Table as t2 On t2.String Like t.String + '%'
Group By t.Id
Order By t.Id