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

You can check my BLOG here

;with spc (id, num) as
(select		a.id, 0
from @vTable a
union all
select a.id, t.num
from @vTable a
inner join tally t
on t.num <= len(a.val) and SUBSTRING(a.val,t.num,1) = ' '
)
select a.id, substring(v.val, a.num + 1, coalesce(b.num-1, len(v.val)) - a.num) as Val
from @vTable v
inner join spc a on a.id = v.id
left join spc b on b.id = a.id and b.num > a.num
and not exists (
select 1
from spc c
where c.id = b.id and c.num > a.num and c.num < b.num
)
order by a.id, a.num

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

Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd')
Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3') 
 
;with spc (id, num) as
(select         a.id, 0
from @vTable a
union all
select a.id, t.num
from @vTable a
inner join tally t
on t.num <= len(a.val) and SUBSTRING(a.val,t.num,1) = ' '
)
select a.id, substring(v.val, a.num + 1, coalesce(b.num-1, len(v.val)) - a.num) as Val
from @vTable v
inner join spc a on a.id = v.id
left join spc b on b.id = a.id and b.num > a.num
and not exists (
select 1
from spc c
where c.id = b.id and c.num > a.num and c.num < b.num
)
order by a.id, a.num

;with wcte (id,val,strpos) as
(
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) 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) 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 
order by id
OPTION (MAXRECURSION 500 )

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

You can check my BLOG here

;with spc (id, val, num, k) as (
select a.id, a.val, t.num, ROW_NUMBER() over (partition by a.id order by t.num) as k 
from @vTable a inner join tally t on t.num <= len(a.val)+2 and SUBSTRING(' '+a.val+' ',t.num,1) = ' '
)
select a.id, substring(a.Val, a.num, b.num- 1 - a.num) from spc a inner join spc b on b.id = a.id and b.k = a.k+1 

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 )

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

You can check my BLOG here