Would it be ok for you to use recursive CTE ? ..something like

create table #Something

(

CityRank int,

Country varchar(10),

City varchar(200)

)

insert #Something

select 1, 'india', 'hyderabad, bangalore, delhi, shimla' union all

select 2, 'usa', 'newjersey, newyork, washington, texas' union all

select 3, 'uk', 'london, greenland, denmark, italy, spain'

--SELECT * FROM #Something

;WITH CTE AS

(

SELECT CityRank,Country,City,SUBSTRING(CITY,1,CHARINDEX(',',CITY,1)-1) AS C_CITY,

SUBSTRING(CITY,CHARINDEX(',',CITY,1)+1,LEN(CITY)) AS C_STR FROM #Something

UNION ALL

SELECT CityRank,Country,City,LTRIM(RTRIM(SUBSTRING(C_STR,1,CASE

WHEN CHARINDEX(',',C_STR,1) = 0 THEN LEN(C_STR)

ELSE CHARINDEX(',',C_STR,1) - 1

END ))),

SUBSTRING(C_STR,CASE

WHEN CHARINDEX(',',C_STR,1) = 0 THEN NULL

ELSE CHARINDEX(',',C_STR,1)+1

END,CASE

WHEN LEN(C_STR) = 0 THEN 1

ELSE LEN(C_STR)

END) FROM CTE

WHERE CHARINDEX(',',C_STR,1) >= 0 --OR

)

SELECT CityRank,Country,C_CITY AS CITY FROM CTE ORDER BY CityRank

I'm sorry for the formatting/colors ..as this is my first post and do not know how to post with SQL formatting being intact