Get the first letter of each word in a SQL string

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

Solution 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

Solution 2

Assuming we're doing this in MSSQL2008R2 though nothing involved should really matter here. All we do is have some fun with string manipulation. You could put this into a funciton or proc or just run it in query analyzer directly.

DECLARE @str varchar(250) = 'Michael Joseph Jackson' 
DECLARE @initials varchar(250) = substring(@str,1,1)

WHILE(charindex(' ',@str)!=0)
BEGIN
    DECLARE @currentSpace int = charindex(' ',@str)
    SET @initials += substring(@str,@currentSpace+1,1)
    SET @str = substring(@str,@currentSpace+1,len(@str))
END

SELECT @initials

If you're not doing this for some trivial purpose you'll likely want to clean up the data before attempting to process it. Names are often prefixed by titles, data entry fields are susceptible to user error, etc.

Solution 3

You'll want to add some checks and error handling before you update tblStudents or something, but this should get you started.

CREATE FUNCTION initials ( @s AS nvarchar(4000))
RETURNS nvarchar(100)
AS
BEGIN
    DECLARE @i nvarchar(100) = LEFT(@s, 1); -- first char in string
    DECLARE @p int = CHARINDEX(' ', @s); -- location of first space
    WHILE (@p > 0) -- while a space has been found
    BEGIN
        SET @i = @i + SUBSTRING(@s, @p + 1, 1) -- add char after space
        SET @p = CHARINDEX(' ', @s, @p + 1); -- find next space
    END 
    RETURN @i
END
GO

SELECT dbo.initials('Michael Joseph Jackson');

Comments

[Irfy]

He means of each word in a whitespace separated string, apparently.

[Clockwork-Muse]

And how are you planning on using that to get subsequent first initials? If you look at his example, there are multiple initials returned. And how are you planning on determining where the next word starts?