last index of character in string?

hello everyone.
i've got a "name" column that i would like to split into "firstname"
and "lastname" columns. the firstname was no problem (the values have
already been LTRIM and RTRIM'd):
UPDATE test
SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
however i'm curious about how to do the last name. you see, there might
be an arbitrary number of ' ' characters in the "name" column values
(in fact the max is seven). however regardless of how many spaces
occur, or what's inbetween those spaces.
now, i can set up a very complicated nested CHARINDEX/SUBSTRING
analysis for each number of ' ' characters that occur, starting with
the seven space condition, and working my way down. but good grief that
would be complicated.
is there any nifty tricks to find the LAST index of a specified
character in a string, by chance? that would save me a world of hurt.
thanks!
jason> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
How about CHARINDEX(' ', REVERSE(TRIM(name)))
However saving you even more of a world of hurt (e.g. not changing the names
of people like Larry da Costa and Filipe de Simone), you should just grab an
off-the-shelf package for data scrubbing. These have much more advanced
rules, and a much higher success ratio, than anything anyone will be able to
write in T-SQL alone.
A|||Try using reverse to flip the string around
UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
Archer
"jason" wrote:

> hello everyone.
> i've got a "name" column that i would like to split into "firstname"
> and "lastname" columns. the firstname was no problem (the values have
> already been LTRIM and RTRIM'd):
> UPDATE test
> SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
> however i'm curious about how to do the last name. you see, there might
> be an arbitrary number of ' ' characters in the "name" column values
> (in fact the max is seven). however regardless of how many spaces
> occur, or what's inbetween those spaces.
> now, i can set up a very complicated nested CHARINDEX/SUBSTRING
> analysis for each number of ' ' characters that occur, starting with
> the seven space condition, and working my way down. but good grief that
> would be complicated.
> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
> thanks!
> jason
>|||Jason,
Could you use something like this'
declare @.name varchar(50),
@.FirstName varchar(25),
@.lastName varchar(25)
Set @.name = 'Joe Bloggs'
Set @.firstname = ltrim(rtrim(LEFT(@.name, CHARINDEX(' ', LTRIM(@.name),
1) - 1)))
Set @.LastName =ltrim(rtrim(Right(@.Name, (Len(@.Name) -
Len(@.FirstName)))))
select @.name, @.FirstName, @.lastName
HTH
Barry|||Sorry, it probably should be
UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)
Archer
"bagman3rd" wrote:
> Try using reverse to flip the string around
> UPDATE test
> SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
> Archer
> "jason" wrote:
>|||> How about CHARINDEX(' ', REVERSE(TRIM(name)))
by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)|||AHA! *REVERSE* that's exactly what I was hoping existed. Took a little
tweaking like so:
UPDATE test
SET lastname = REVERSE(LEFT(REVERSE(name), CHARINDEX(' ',
REVERSE(name), 1) - 1))
But that worked like a charm! Thanks very much.
As for the value of the data, it's not worth any scrubbing of note, but
I do appreciate the suggestion for cases that might be.
Jason