----------------------------------------------------------------
--- Create a table of numbers.
if object_id('tempdb.dbo.#Numbers') is not null drop table dbo.#Numbers;
create table dbo.#Numbers(n int not null primary key clustered);

--- insert 10000 numbers into it. (must be greater than the sum total
--- of characters in every row of the phone names)
declare @max int; set @max = 10000;

with a as (select top (cast(sqrt(@max)+1 as int)) number as N  from master..spt_values)
insert into #Numbers
select top (@max) row_number() over (order by a.N) from A cross join A b 

GO
----------------------------------------------------------------
-- create test data with phone names

if object_id('tempdb.dbo.#phones') is not null drop table #phones;   
create table #phones (phoneNames varchar(255)); 
insert into #phones values ('Motorola Samsung Nokia LG');
insert into #phones values ( 'Apple    Nokia'); -- check: if extra spaces work correctly
insert into #phones values ( 'Airtel   LG Apple BSNL Motorola');
insert into #phones values ( 'ABC ABC Motorola'); -- check: two ABCs should count as 2

GO

----------------------------------------------------------------
-- Need a separator other than space because xml mistreats space.
declare @sep varchar(1); set @sep = '|';

-- concatenate rows into @var with each name separated by the separator.
declare @var varchar(max);
select @var = 
(
   select 
      @sep as [text()],
      replace(phoneNames,' ',@sep) as [text()]
   from #phones
      for xml path ('')
)+ @sep ;

-- split the concatenated var into a table
with CTE(name) as 
(
   select
      nullif(subString(@sep + @var + @sep , n , charindex(@sep ,@sep + @var + @sep , n) - n) , '') 
   from 
      #Numbers
   where 
      n <= len(@sep + @var + @sep) 
      and subString(@sep + @var + @sep , n - 1, 1) = @sep
      and charIndex(@sep , @sep + @var + @sep , n) - n > 0
) 
-- and count records in the table.
select name,count(*) as phones from CTE group by name order by name

----------------------------------------------------------------

if object_id('tempdb.dbo.#Numbers') is not null drop table dbo.#Numbers;
if object_id('tempdb.dbo.#phones') is not null drop table #phones;   
----------------------------------------------------------------