Concatenate Initials from First Names in SQL

caandal
caandal used Ask the Experts™
on
Hi Experts

I have written a query that concatenates Initials from a filed called PrincipalFirstNames in SQL.

It works but surely there must be an easier way to do it.  
Thanks
Alan
Here it is:

SELECT  PrincipalFirstNames ,
        CASE WHEN CHARINDEX(' ', PrincipalFirstNames) > 0
             THEN LEFT(PrincipalFirstNames, 1) + SUBSTRING(PrincipalFirstNames,
                                                           CHARINDEX(' ',
                                                              PrincipalFirstNames)
                                                           + 1, 1)
             ELSE LEFT(PrincipalFirstNames, 1)
        END
        + CASE WHEN CHARINDEX(' ', PrincipalFirstNames,
                              CHARINDEX(' ', PrincipalFirstNames) + 1) > 0
               THEN SUBSTRING(PrincipalFirstNames,
                              CHARINDEX(' ', PrincipalFirstNames,
                                        CHARINDEX(' ', PrincipalFirstNames)
                                        + 1) + 1, 1)
               ELSE ''
          END
          + CASE WHEN CHARINDEX(' ', PrincipalFirstNames,
                              CHARINDEX(' ', PrincipalFirstNames) + 1) > 0
               THEN SUBSTRING(PrincipalFirstNames,
                              CHARINDEX(' ', PrincipalFirstNames,
                                        CHARINDEX(' ', PrincipalFirstNames)
                                        + 1) + 1, 1)
               ELSE ''
          END AS Initials
FROM    Policy
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Make a Function, test it, forget about it

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION FnInitials 
(
	-- Add the parameters for the function here
	@ForeNames nvarchar(400)
)
RETURNS nvarchar(20)
AS
BEGIN
	DECLARE @RETVAL nvarchar(20);
	DECLARE @I int;
	
	
	SET @I = 1;
	
	
	
	WHILE @I <= LEN(@ForeNames)
	BEGIN
		IF @I = 1 
			SET @RETVAL = COALESCE(@RETVAL, '') + SUBSTRING(@ForeNames, @I, 1) + ' ';
		ELSE
			IF SUBSTRING(@ForeNames, @I - 1, 1) = ' ' AND SUBSTRING(@ForeNames, @I, 1) <> ' ' 
				SET @RETVAL = @RETVAL + SUBSTRING(@ForeNames, @I, 1) + ' ';			
		
		SET @I = @I + 1;
	
	END 		

	RETURN RTRIM(@RETVAL)

END
GO

Open in new window


Then

SELECT dbo.fninitials('ANDREW JAMES WILLIAM    THOMAS')
CREATE FUNCTION FnInitials

in that, rather than ALTER FUNCTION of course, sorry

Author

Commented:
Thanks Dieghton - thought that there must be an easier way

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial