Problem With SUBSTRING

  • Hello,

    This should be very simple. I have the classic column called 'FullName' where the data contains 'last name, first name'. I've tried the following to pull out the first name:

    SUBSTRING (FullName, 1, (CharIndex(',',FullName)-1))AS LastName

    When I try this, I get this message: 'Invalid length parameter passed to the SUBSTRING function'. Subtracting 1 in the third argument causes the problem, but I need to eliminate the comma character.

    What combination of string functions should I be using to pull out first and last name into seperate columns? It seems like this should be a common need, but I really haven't been able to find anything through Google.

    Thank you for your help!

    CSDunn

  • Your issue is likely due to some of your full names not having a comma. That would result in CHARINDEX returning 0, and you passing a negative length into the substring.

    Try filtering you update only on FULLNAMES with a comma

    ... WHERE CHARINDEX(',',FULLNAME)>0 ...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I've occassionally had problems with the where clause being resolved after the attempt at the string function. This can be resolved by forcing it into two steps in the sequence you need. I do this by using a CTE to query the columns and add the Where clause, then use the string function on the CTE in a second CTE or the main query.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for your help!

    CSDunn

  • Instead of puting the check in the where clause, why not use a case statement in the select to filter this. You will have to decide if the fullname is missing a ',' where the first name is. Is it the first name in the fullname or the second.

    if it is the first

    CASE WHEN CHARINDEX(',', fullname, 1) > 0 THEN SUBSTRING(fullname, CHARINDEX(',', fullname, 1) + 2, LEN(fullname) - CHARINDEX(',', fullname, 1) -1) ELSE SUBSTRING(fullname, 1, CHARINDEX(' ', fullname, 1) END

    or if is the second

    CASE WHEN CHARINDEX(',', fullname, 1) > 0 THEN SUBSTRING(fullname, CHARINDEX(',', fullname, 1) + 2, LEN(fullname) - CHARINDEX(',', fullname, 1) -1) ELSE SUBSTRING(fullname, CHARINDEX(' ', fullname, 1) + 1, LEN(fullname)) END

    Dave Novak

Viewing 5 posts - 1 through 4 (of 4 total)

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