Results 1 to 3 of 3

Thread: FormatName

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Question Unanswered: FormatName

    I am currently pulling the following field in my query:

    isnull(dbo.FormatName(refdr.Prefix, refdr.First, refdr.Middle, refdr.Last, refdr.Suffix),'')AS Ref_DoctorName

    When the field is NULL, its returning a "," value instead of a NULL as I need. Any suggestions on a better format?

  2. #2
    Join Date
    Feb 2008
    Posts
    2
    FormatName function is currently as follows:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER FUNCTION FormatName
    (@pPrefix VARCHAR(10),
    @pFirst VARCHAR(30),
    @pMiddle VARCHAR(30),
    @pLast VARCHAR(30),
    @pSuffix VARCHAR(20)
    )
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @pf VARCHAR(10), @fn VARCHAR(30), @mi VARCHAR(30), @ln VARCHAR(30), @sf VARCHAR(20)
    SET @pf = ISNULL(@pPrefix, '')
    SET @fn = ISNULL(@pFirst, '')
    SET @mi = ISNULL(@pMiddle, '')
    SET @ln = ISNULL(@pLast, '')
    SET @sf = ISNULL(@pSuffix, '')

    IF LEN(@pf) < 1
    SET @pf = NULL
    IF LEN(@fn) < 1
    SET @fn = NULL
    IF LEN(@mi) < 1
    SET @mi = NULL
    IF LEN(@ln) < 1
    SET @ln = NULL
    IF LEN(@sf) < 1
    SET @sf = NULL

    RETURN RTRIM
    (
    LTRIM
    (
    ISNULL(@ln,'')
    + CASE WHEN @sf IS NOT NULL THEN ' ' + @sf ELSE '' END + ', '
    + CASE WHEN @pf IS NOT NULL THEN @pf + ' ' ELSE '' END
    + CASE WHEN @fn IS NOT NULL THEN @fn + ' ' ELSE '' END
    + LTRIM( ISNULL(@mi,'') )
    )
    )
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Code:
    ISNULL(@ln,'')
    + CASE WHEN @sf IS NOT NULL THEN ' ' + @sf ELSE '' END + ', '
    Oh look ... you're concatinating a comma after the null check for @sf ... so if all your other variables are null, your whole output will be a "," just like you are getting ... darn computer doing what you told it to do instead of what you want it to do

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •