Striping characters

[pto]

I have a field 'FirstName' that is 9 characters long. Some firstnames take all 9 characters and some do not take all the characters and have the middle initial. I want to strip the middle inital where it exist and where it does not exist skip and move on to the next.

I used the folowing to strip the middle inital which seemed to work initally, but names with no space I get the whole firstname.

select substring(BS.FIRSTNAME,charindex(' ',BS.FIRSTNAME)+1,len(BS.FIRSTNAME)) as [MI]
from BLUESHIELD BS

 

[PurpleOne]

If you use the Case function you can check for a value of 0 - which means not found - for example

select case Charindex(' ', BS.FirstName)

when 0 then BS.FirstName
else substring(BS.FirstName, 1 , charindex(' ',BS.FirstName)-1)
end
from tblreportcpresults BS

will give you the first name stripped of anything following the first space found - not quite the same as middle initial.

 

[pto]

Thanks, I will muck with it and see if I can get the middle initial.