sql server 2005 - sql to pick apart a string of a persons name and output the initials

Question

how can i get SQL to take a sting and return the first letter of each word passed into it.

I want to use this UDF for generating initials for peoples names I have in the DB.

names can be 2 (fname, lname)or 3(...mname) words

i am using sql2005

Answer 1

This should work for both "Firstname Lastname" and "Firstname Middlename Lastname" combinations.

DECLARE @name AS NVARCHAR(50) 
SET @name = 'Firstname Middle Lastname' 


SELECT SUBSTRING(@name, 1, 1) +     --First initial
    SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 1) +    --Middle/Last initial
    CASE WHEN 0 <>  CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) -- More than two words 
        THEN SUBSTRING(@name, CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) + 1, 1)  --Last initial
    ELSE '' --Have to add empty string to avoid NULLing entire result
    END

Of course, if users have a space in one of their names for some reason you will have an issue parsing this out, but I suspect that would be the case anyways when not storing your names in separate fields.