0

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
  • 1
    Are your prefixes a finite set? – SqlWorldWide May 16 '17 at 12:42
  • how do you consider 'Mr.Raju' one word or two words? – McNets May 16 '17 at 12:44
  • Will there ever be leading spaces? – Erik Darling May 16 '17 at 14:04
2

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     |
+--------+------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.