Technical Article

Display Person Initials

,

Display a person's initials regardless if they have a middle name or not.

Just pass in the persons name

CREATE FUNCTION ufnDerive_Initials

(@stringtobesearched varchar(8000))

RETURNS char(3) AS

BEGIN



DECLARE @FirstSpace INT
DECLARE @SecondSpace INT
DECLARE @ReversedYN INT
DECLARE @Results VARCHAR(3)

SET @ReversedYN = CHARINDEX(',', RTRIM(@stringtobesearched))
SET @stringtobesearched = REPLACE(@stringtobesearched, ',', ' ')
SET @FirstSpace = CHARINDEX(' ', RTRIM(@stringtobesearched))

IF @FirstSpace < LEN(RTRIM(@stringtobesearched)) 

BEGIN

                        -- Write first letter of name
                        SET @Results = SUBSTRING(RTRIM(@stringtobesearched),1, 1)

                        SET @Results = @Results + SUBSTRING(RTRIM(@stringtobesearched),@FirstSpace + 1, 1)

                        -- Now check to see if there is a second space in the string
                        SET @SecondSpace = CHARINDEX(' ', RTRIM(@stringtobesearched),@FirstSpace + 1)

                        IF @SecondSpace > 0

                        BEGIN

                                    SET @Results = @Results + SUBSTRING(RTRIM(@stringtobesearched),@SecondSpace + 1, 1)

                        END

                        -- If @SecondSpace = 0 then No second space, we are done

END

ELSE

-- No middle inital or last name write first letter of string only

BEGIN

            SET @Results = RTRIM(LTRIM(SUBSTRING(RTRIM(@stringtobesearched),1,1)))
    RETURN @Results
END


IF @ReversedYN > 0 

BEGIN
            SELECT @Results = REVERSE(@Results)
    RETURN @Results
END

ELSE

BEGIN
            RETURN @Results
END


RETURN @Results

END

Read 1,530 times
(14 in last 30 days)

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating