Splitting of column values

Question

hi everyone i am having the below input table INPUT Name Hari;shanker;singh Hari;shanker Hari need to get the output like below please let me know OUTPUT Name count Shanker 2 Hari 3 Singh 1 Thanks in Advance

Hari Shanker · Answer

Hi Abhishek I need the Splitting Column values count ..as i have shown above Thanks hari

Akshay Phadke · Answer

SELECT CASE WHEN name LIKE '% %' THEN LEFT(name Charindex(' ' name) - 1) ELSE name END CASE WHEN name LIKE '% %' THEN RIGHT(name Charindex(' ' Reverse(name)) - 1) END FROM YourTable