Thanks for pointing Doug. Try this;

Declare @vTable Table(id int,val varchar(Max))

Insert into @vTable Values (1,'Atif1 SS')

Insert into @vTable Values (2,'Asif2 SS')

;with wcte (id,val,strpos) as

(

Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val)+1 else charindex(' ',val,0) + 1 end) as val

,charindex(' ',val,0) + 1 strpos

from @vTable

Union all

Select b.id,substring(b.val,strpos,case when charindex(' ',b.val,strpos) = 0 then Len(b.val)+1 else charindex(' ',b.val,strpos) - strpos end) as val

,charindex(' ',b.val,strpos) + 1 strpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.strpos <> 1

)Select id,Val from wcte

where Val <> ''

order by id

OPTION (MAXRECURSION 500 )

I have replaced Len(Val) with Len(Val) + 1 to get the required outout.