 <p>I did it. Thanks for your help</p> <pre><code> declare @name varchar(100) set @name='Sharma,Ravi,K' select CASE WHEN CHARINDEX(' ',REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1) &gt;0 THEN SUBSTRING(REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1,(LEN(@name)-CHARINDEX(' ',REVERSE(REPLACE(@name,',',' '))))-CHARINDEX(' ',REPLACE(@name,',',' '))) else SUBSTRING(REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1,LEN(@name)) END AS 'FIRST NAME' ,CASE WHEN CHARINDEX(' ',REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1) &gt;0 THEN SUBSTRING(REPLACE(@name,',',' '),LEN(@name)-CHARINDEX(' ',REVERSE(REPLACE(@name,',',' ')))+2,LEN(@name)) ELSE ' ' END AS 'MIDDLE NAME' , SUBSTRING(REPLACE(@name,',',' '), 1,CHARINDEX(' ',REPLACE(@name,',',' '))) </code></pre>