last index of character in string?

9 Sep 2005 6:00 PM

jason

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

9 Sep 2005 6:11 PM

bagman3rd

Try using reverse to flip the string around

UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)

Archer

Show quote

"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
>
>

9 Sep 2005 6:16 PM

bagman3rd

Sorry, it probably should be

UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)

Archer

Show quote

"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:
>
> > 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
> >
> >

9 Sep 2005 6:12 PM

Barry

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

9 Sep 2005 6:13 PM

Aaron Bertrand [SQL Server MVP]

> 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

9 Sep 2005 6:32 PM

Aaron Bertrand [SQL Server MVP]

> How about CHARINDEX(' ', REVERSE(TRIM(name)))

by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)

9 Sep 2005 6:34 PM

jason

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