Post reply

Get Full Name and Last Name in Report

  • December 2, 2021 at 1:40 am

    #3959370

    Hello, i have tried multiple expressions from websites and couldn't get the real answer. What is the full expression that gives me the First and Last Name in SSRS expression: For ex: John Andrew Brown Hill = John Hill.

  • December 2, 2021 at 3:45 am

    #3959390

    I don't there is a reliable way of doing this in SSRS or T-SQL. It's just that some names have spaces in them etc. They're not standard, so a pure code answer may not always work. You could always use the standard CHARINDEX or InStr() (or whatever it's called in SSRS) to get the position of the space and get all characters to one side of it, using LEFT or RIGHT. This worked in T-SQL:

    CREATE TABLE #Test( fullname varchar(25) not null);
    GO
    INSERT INTO #Test VALUES ('John Andrew Brown Hill');

    SELECT c2.FullName
    , c2.Item
    , FName = FIRST_VALUE(c2.item) OVER (PARTITION BY c2.fullname ORDER BY c2.itemNumber ASC)
    , LName = FIRST_VALUE(c2.item) OVER (PARTITION BY c2.FullName ORDER BY c2.ItemNumber DESC)
    FROM
    (
    SELECT t.FullName, ca.ItemNumber, ca.Item
    FROM #Test t
    CROSS APPLY Testdb.dbo.DelimitedSplit8K(t.FullName, ' ') ca
    ) c2;

    I used Jeff Moden's DelimitedSplit8K function...

    • This reply was modified 3 years ago by  pietlinden.

  • December 2, 2021 at 12:01 pm

    #3959523

    Thank you. I have done what you said when importing dataset though SSRS and it's much simpler then to do anything code related in SSRS expression.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply