Lastname, Firstname switch

  • I dont know if this is any help but this is the code I started with:

    SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, HOST0140.NAME, dateadd(mi, HOST0120.STARTMINS, HOST0120.MTGDATE) AS ASTART,

    dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) AS AFINISH, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.STARTMINS,

    HOST0120.MTGDATE) ,108) AS START, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) ,108) AS FINISH,

    HOST0120.MTGKEY, HOST0120.HIPTYPE, HOST0120.ROOMKEY

    FROM HOST0140

    INNER JOIN HOST0120

    ON HOST0120.OWNERKEY=HOST0140.PERSONKEY

    INNER JOIN HOST9006

    ON HOST9006.KEYVALUE=HOST0120.MTGSTATE

    INNER JOIN HOST0110

    ON HOST0110.ROOMKEY=HOST0120.ROOMKEY

    WHERE CANCELSTATE='0'

    AND MTGDATE >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    AND MTGDATE < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

    AND MTGSTATE <> '11'

    ORDER BY START

    It returns everything I want it to. The issue is that the HOST0140.NAME returns the value as:

    Lastname, Firstname

    I desperatly need the code above to be adapted to reverse this and remove the comma. So the NAME column shows:

    Firstname Lastname

    I am tearing my haid out at the moment lol.

    Many Thanks

  • Try this. If it works fine, make it a UDF and call it passing in your name field.

    declare

    @Input varchar(200)

    ,@Delimiter varchar(5)

    ,@Output varchar(150)

    set @Input = 'Doe, John'

    set @Delimiter = ','

    WHILE LEN(@Input) > 0

    BEGIN

    IF CHARINDEX(@Delimiter, @Input) > 0

    BEGIN

    SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')

    SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))

    END

    ELSE

    BEGIN

    SET @Output = @Input + ' ' + ISNULL(@Output,'')

    SET @Input = ''

    END

    END

    print SUBSTRING(@Output,0,LEN(@Output))

    Mark

  • Hi,

    Thanks for your reply. Unfortunatlly i dont even know what a UDF is...

    I am extreamlly new to SQL - I am doing this as a one off and need to get it working in the next two hours. I may well loose my mind soon lol.

    THanks

  • Sorry about that.

    UDF = User Defined Function

    Execute this in your database:

    create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))

    returns varchar(100)

    as

    begin

    declare

    @Output varchar(150)

    WHILE LEN(@Input) > 0

    BEGIN

    IF CHARINDEX(@Delimiter, @Input) > 0

    BEGIN

    SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')

    SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))

    END

    ELSE

    BEGIN

    SET @Output = @Input + ' ' + ISNULL(@Output,'')

    SET @Input = ''

    END

    END

    return SUBSTRING(@Output,0,LEN(@Output))

    end

    This will create the UDF for you.

    And then change this part of your select query:

    HOST0140.NAME

    To this:

    dbo.udf_ReverseNames(HOST0140.NAME, ',') as Name

    That should return the name in reverse and strip off the comma.

    Mark

  • Mark Eckeard (9/16/2012)


    Sorry about that.

    UDF = User Defined Function

    Execute this in your database:

    create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))

    returns varchar(100)

    as

    begin

    declare

    @Output varchar(150)

    WHILE LEN(@Input) > 0

    BEGIN

    IF CHARINDEX(@Delimiter, @Input) > 0

    BEGIN

    SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')

    SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))

    END

    ELSE

    BEGIN

    SET @Output = @Input + ' ' + ISNULL(@Output,'')

    SET @Input = ''

    END

    END

    return SUBSTRING(@Output,0,LEN(@Output))

    end

    This will create the UDF for you.

    And then change this part of your select query:

    HOST0140.NAME

    To this:

    dbo.udf_ReverseNames(HOST0140.NAME, ',') as Name

    That should return the name in reverse and strip off the comma.

    Mark

    Oh, be careful now, Mark. The use of While loops here does two bad things...

    1. They themselves are slow.

    2. To use them in a UDF requires that the UDF be slower because it will be either a Scalar UDF or a Multi-Line Table Value Function. It really needs to be written as an "iSF".

    Please see the following article for more on all of that.

    http://www.sqlservercentral.com/articles/T-SQL/91724/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here's a different method that's faster. Of course, I can't claim performance improvements unless I can prove it. To do that, we'll setup a 100,000 row test table, like this...

    --===== Conditionally drop and repopulate the test table

    -- to make reruns in SSMS easier. We're just building

    -- test data here. THIS IS NOT A PART OF THE SOLUTION.

    IF OBJECT_ID('tempdb..#HOST0140','U') IS NOT NULL

    DROP TABLE #HOST0140

    ;

    WITH

    cteTally AS

    (

    SELECT TOP 100000

    N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    )

    SELECT NAME = 'LastName'+CAST(N AS VARCHAR(10))

    + ', '

    + 'FirstName'+CAST(N AS VARCHAR(10))

    INTO #HOST0140

    FROM cteTally

    ;

    Here's an iTVF being used as an iSF (see http://www.sqlservercentral.com/articles/T-SQL/91724/

    for more on those)...

    CREATE FUNCTION dbo.ReverseName

    (@pString VARCHAR(8000),@pDelimiter VARCHAR(5))

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH cteFindDelimiter AS

    (

    SELECT DelimiterPosition = CHARINDEX(@pDelimiter,@pString)

    )

    SELECT ReversedName = CASE

    WHEN DelimiterPosition > 0

    THEN LTRIM(SUBSTRING(@pString,DelimiterPosition+LEN(@pDelimiter),8000))

    + ' '

    + SUBSTRING(@pString,1,DelimiterPosition-1)

    ELSE @pString

    END

    FROM cteFindDelimiter

    ;

    Now, a test to compare the two. The @Bitbucket variable takes the display time out of the picture.

    --===== Declare a timer variable.

    DECLARE @StartTime DATETIME;

    --===== Create variable to take display times out of the picture.

    DECLARE @Bitbucket VARCHAR(8000);

    RAISERROR('========== ReverseName ========================================',0,1)

    SELECT @StartTime = GETDATE();

    SELECT @Bitbucket = r.ReversedName

    FROM #HOST0140 h

    CROSS APPLY dbo.ReverseName(h.Name,',') r;

    PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));

    RAISERROR('========== udf_ReverseNames ===================================',0,1)

    SELECT @StartTime = GETDATE();

    SELECT @Bitbucket = dbo.udf_ReverseNames(h.Name,',')

    FROM #HOST0140 h;

    PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));

    Here are the results from that test. "ReverseName" is new function with no While Loop.

    ========== ReverseName ========================================

    Duration (ms): 450

    ========== udf_ReverseNames ===================================

    Duration (ms): 2123

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Forgive my innocent question here but why a FUNCTION at all?

    Just replace:

    HOST0140.NAME

    with:

    NAME=RIGHT(HOST0140.NAME, LEN(HOST0140.NAME)-(1+CHARINDEX(', ', HOST0140.NAME))) + ' ' +

    LEFT(HOST0140.NAME, CHARINDEX(', ', HOST0140.NAME) - 1)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Jeff - thanks, I'll keep this in mind in case I have to do something similar in the future.

    The advantage to making it a UDF is for code re-use. I was thinking he may need to this in other places and it would make it easier although he could certainly use reverse the words directly in his query if necessary.

    Mark

  • Mark Eckeard (9/17/2012)


    Jeff - thanks, I'll keep this in mind in case I have to do something similar in the future.

    The advantage to making it a UDF is for code re-use. I was thinking he may need to this in other places and it would make it easier although he could certainly use reverse the words directly in his query if necessary.

    Mark

    Thanks for the feedback, Mark. I absolutely agree that it should be a UDF for the very reasons you stated. That's why I included my code as a UDF... just not a scalar one. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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