Split the Data in the Column into 2 using sql server

Question

Input Table
------------
IBM   India Limited
IBM   US    Limited
TATA  UK    Limited

output should be as follows

-------------------------
Company     Country
---------   -------
IBM          India
IBM          US
TATA         UK

Answer 1

Use Parsename trick to do this.

SELECT Parsename(Replace(Columnname, ' ', '.'), 3) Company,
       Parsename(Replace(Columnname, ' ', '.'), 2) Country
FROM   tablename 

Answer 2

Try this:

declare @t table (company varchar(100));

insert into @t  values('IBM India Limited')
insert into @t values('IBM US Limited')
insert into @t values('TATA UK Limited')

select 
    case when CHARINDEX(' ',company)>0 
         then SUBSTRING(company,1,CHARINDEX(' ',company)-1) 
         else company end companyName, 
    CASE WHEN CHARINDEX(' ',company)>0 
         THEN SUBSTRING(company,CHARINDEX(' ',company)+1,len(company)-12)  
         ELSE NULL END as CountryName
from @t;

Result:

companyName     CountryName
---------------------------
IBM             India
IBM             US
TATA            UK