last position of the specified expression in a character string

Hi,
could you help me. I'd like to get last position of the specified expression in a character string(a fast solution for DB)
Thanks for youe helpcould you give an example please? which specified expression? what character string?|||This will probably involve some creative use of the REVERSE function, but give us some more details, please.|||yeah, REVERSE, that's what i was gonna say, with actual sql :D|||Darn, I was going to suggest Reverse() and Patindex() :(|||yeah, with one invokation of DATALENGTH() as wellsql

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

Largest character data type?

I thought "text" was the largest, as the documentation says the max length
is (2^31)-1
But I can only fit 8000 characters in before it truncates, but even then it
is not exactly 8000 characters, but more like 1000.TEXT is the largest data type. Where do you see the truncation happening'
If it is in Query Analyzer, you need to expand the number of display
character (max is 8192). Your application will never truncate the content of
the TEXT data type.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:OSh09QhvDHA.2076@.TK2MSFTNGP09.phx.gbl...
> I thought "text" was the largest, as the documentation says the max length
> is (2^31)-1
> But I can only fit 8000 characters in before it truncates, but even then
it
> is not exactly 8000 characters, but more like 1000.
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> TEXT is the largest data type. Where do you see the truncation happening'
> If it is in Query Analyzer, you need to expand the number of display
> character (max is 8192). Your application will never truncate the content
of
> the TEXT data type.
I see this from the table view, when I 'return all rows' I can scroll
through the field, but it only seems to have stored to a certain point.
The result is visible here:
http://www.wmjackson.cable.nu/website/goto.aspx?id=0|||That must be a limitation with Enterprise Manager. The data will not be
truncated.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:eOaaYPjvDHA.1512@.TK2MSFTNGP10.phx.gbl...
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > TEXT is the largest data type. Where do you see the truncation
happening'
> > If it is in Query Analyzer, you need to expand the number of display
> > character (max is 8192). Your application will never truncate the
content
> of
> > the TEXT data type.
> I see this from the table view, when I 'return all rows' I can scroll
> through the field, but it only seems to have stored to a certain point.
> The result is visible here:
> http://www.wmjackson.cable.nu/website/goto.aspx?id=0
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OEnSpbjvDHA.3256@.TK2MSFTNGP11.phx.gbl...
> That must be a limitation with Enterprise Manager. The data will not be
> truncated.
Actually, that makes sense.