Get the first letter of each word in a SQL string [duplicate]

Question

Possible Duplicate:
sql to pick apart a string of a persons name and output the initials

In MS-SQL Server, there is a way to get the first letter of each word in a string? For example:

Name:

Michael Joseph Jackson

Query:

SELECT name, [function] as initial FROM Customers

Result:

MJJ

Answer 1

This function will shield your results against multiple sequential spaces in the source string:

CREATE FUNCTION dbo.fnFirsties ( @str NVARCHAR(4000) )
RETURNS NVARCHAR(2000)
AS
BEGIN
    DECLARE @retval NVARCHAR(2000);

    SET @str=RTRIM(LTRIM(@str));
    SET @retval=LEFT(@str,1);

    WHILE CHARINDEX(' ',@str,1)>0 BEGIN
        SET @str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
        SET @retval+=LEFT(@str,1);
    END

    RETURN @retval;
END
GO

SELECT dbo.fnFirsties('Michael Joseph Jackson');
SELECT dbo.fnFirsties('  Michael   Joseph Jackson  '); -- multiple space protection :)

Results:

MJJ
MJJ