TSQL Extract first word as First name. If only one word or null, set to ‘’ Table column has values it includes prefix as well. I want to extract first word as First_name. If only one word or null, set to ''.

Name
----------
Abhijit K
Ravi Mahta
Dr.Rajiv
Dr sharma
Mr.Raju
Miss Anjali


Output I want:

First_name
----------
Abhijit 
Ravi 
Rajiv
sharma
Raju
Anjali
This is a little different than what your stated goals were, but it might be what you want.

with cte as (
  select Prefix
  from (values ('Dr '),('Mr '),('Miss ')) v (Prefix)
)
select 
    prefix = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then left(s.Name,charindex(' ',s.Name+' '))
        end
  , first_name = case when left(s.Name,charindex(' ',s.Name+' ')) in (select Prefix from cte)
          then stuff(s.Name,1,charindex(' ',s.Name+' '),'')
        else  nullif(left(s.Name,charindex(' ',s.Name+' ')),'')
        end
from (
  select name = replace(Name,'.',' ')
  from t
    ) s


rextester demo: http://rextester.com/BQQO51384

returns:

+--------+------------+
| prefix | first_name |
+--------+------------+
| NULL   | Abhijit    |
| NULL   | Ravi       |
| Dr     | Rajiv      |
| Dr     | sharma     |
| Mr     | Raju       |
| Miss   | Anjali     |
+--------+------------+