Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: Lastname, Firstname switch

    Hi,

    At present we have a Name table that displays an output as Lastname, Firstname.

    I need this to display as Firstname Lastname. I think im nearly there as per the code below. The only issue is I can get it to display the first and last in seperate columns. As I have stated I need Firstname Lastname to display in the column NAME.

    Here is what I have, please help....


    SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) as FIRSTNAME, SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1)as LASTNAME , 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    SELECT ...
         , RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) as FIRSTNAME
         , SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1)as LASTNAME 
         , ...
    to this --
    Code:
    SELECT ...
         , RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) + ' ' +
           SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1) as NAME 
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Posts
    6
    Hey,

    Thanks for you help. I think that nearly works, but it is still showing an error when addding it to the third party piece of software:

    It should return the firstname lastname from the name field. I think i am missing something...

    SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) + ' ' +
    SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1) as 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

  4. #4
    Join Date
    Sep 2012
    Posts
    6
    The error is:

    Invalid length parameter passed to the LEFT or SUBSTRING function

  5. #5
    Join Date
    Sep 2012
    Posts
    6
    Sorry, full error:

    Msg 537, Level 16, State 2, Line 1
    Invalid length parameter passed to the LEFT or SUBSTRING function.

  6. #6
    Join Date
    Sep 2012
    Posts
    6
    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

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by james.martin View Post
    Sorry, full error:

    Msg 537, Level 16, State 2, Line 1
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    Both functions, RIGHT and SUBSTRING, use CHARINDEX to get the numerical value for second or third part of parameter. It may well be that the condition is not met and a zero is passed into the parameter which will cause this error. Might have to rewrite the Name concat as a case statement to capture the different types of possible values.

    BOL:

    If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.

    If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.

    If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.

  8. #8
    Join Date
    Sep 2012
    Posts
    6
    Hi There,

    Thanks for your reply. However I fear this is a little over my head. I'm new to this and am really struggling. I need to get this working by the end of play today else we cant sign of on a job.

    Thanks

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Here is the problem code snippet:

    Code:
    RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) + ' ' +
    SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1) as NAME
    Try some debugging (remove one of these and run query):

    RIGHT(HOST0140.NAME, CHARINDEX(',', HOST0140.NAME)+2) + ' ' <-- Does this always return results?

    Replace with next code snippet:
    SUBSTRING(HOST0140.NAME, 1,CHARINDEX(', ', HOST0140.NAME)-1) <-- Does this always return results?

    Which one gives the error?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try using the attached UDF function, which will format western-style names just about any way you want.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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