asked on

Concatenate Initials from First Names in SQL

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

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deighton

CREATE FUNCTION FnInitials

in that, rather than ALTER FUNCTION of course, sorry

caandal

ASKER

Thanks Dieghton - thought that there must be an easier way