Error Parsing Name String

I''m reposting this problem because I may not have explained it well enough
before. in example 1 below, i've successfully extracted the last name from a
full name field, although I've hard-coded the fullname or userName.
Example 2 gives the listed error when i run it on my table with data in the
userName field. What I can't figure is Example 2 will work if I don't
include the "-1" part, buh returns the lastname and it's trailing comma.
What am I doing wrong? My data looks like the DATA section below.
--EXAMPLE 1: works , hard-coded name
SELECT 'Miles, Michael L' as userName,
LEFT('Miles, Michael L',CHARINDEX(',','Miles, Michael L')-1) as userLast
-- EXAMPLE 2
-- Error Msg 536, Level 16, State 3, Line 1
-- Invalid length parameter passed to the substring function.
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
DATA Table***********************************
****************
USERNAME
Smith, John R
Walker, L.T.
Adams, Charles
Weathersby, D.J.On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
(snip)
>-- EXAMPLE 2
>-- Error Msg 536, Level 16, State 3, Line 1
>-- Invalid length parameter passed to the substring function.
>SELECT name as userName,
>LEFT(name,CHARINDEX(',',name)-1) as userLast
>FROM sc_employee
Hi Scott,
You probably have at least one row where there is no comma in the Name
column. To find the offenders, use
SELECT name
FROM sc_employee
WHERE name NOT LIKE '%,%'
And to exclude the offending rows from your query, change it to
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
WHERE name LIKE '%,%'
Hugo Kornelis, SQL Server MVP|||I found a null record. thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:6nm0321acbpk9am57qii9nk8p23dinp6a3@.
4ax.com...
> On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
> (snip)
> Hi Scott,
> You probably have at least one row where there is no comma in the Name
> column. To find the offenders, use
> SELECT name
> FROM sc_employee
> WHERE name NOT LIKE '%,%'
> And to exclude the offending rows from your query, change it to
> SELECT name as userName,
> LEFT(name,CHARINDEX(',',name)-1) as userLast
> FROM sc_employee
> WHERE name LIKE '%,%'
> --
> Hugo Kornelis, SQL Server MVP