February 26, 2008 at 9:33 am
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
February 26, 2008 at 9:40 am
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?
February 26, 2008 at 11:43 am
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
February 26, 2008 at 3:00 pm
Thanks for your help!
CSDunn
February 26, 2008 at 3:21 pm
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