## Find the last positon of a character

### Find the last positon of a character

I have a table with one column called [Journal Name] which has a couple of
colons ":" in the field.  I'd like to delete all data after the last colon.
Is there a function to find the last position of a character?

thanks

Daniel Bogesdorfer
Senior Programmer / Analyst
University Of Pittsburgh

### Find the last positon of a character

Not exactly, but REVERSE() and CHARINDEX() together work:

If [Journal Name] is, say, varchar(80),

REVERSE(
SUBSTRING(
REVERSE([Journal Name]),
1+CHARINDEX(':', REVERSE([Journal Name])),
80)
)

is [Journal Name] with everything after the last colon deleted.

Steve

> I have a table with one column called [Journal Name] which has a couple of
> colons ":" in the field.  I'd like to delete all data after the last colon.
> Is there a function to find the last position of a character?

> thanks

> Daniel Bogesdorfer
> Senior Programmer / Analyst
> University Of Pittsburgh

### Find the last positon of a character

A couple of more ways...

SELECT LEFT([Journal Name],LEN([Journal
Name])-CHARINDEX(':',REVERSE([Journal Name])))

SELECT SUBSTRING([Journal Name],1,LEN([Journal
Name])-CHARINDEX(':',REVERSE([Journal Name])))

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

> Not exactly, but REVERSE() and CHARINDEX() together work:

> If [Journal Name] is, say, varchar(80),

> REVERSE(
>   SUBSTRING(
>     REVERSE([Journal Name]),
>     1+CHARINDEX(':', REVERSE([Journal Name])),
>     80)
> )

> is [Journal Name] with everything after the last colon deleted.

> Steve

> > I have a table with one column called [Journal Name] which has a couple
of
> > colons ":" in the field.  I'd like to delete all data after the last
colon.
> > Is there a function to find the last position of a character?

> > thanks

> > Daniel Bogesdorfer
> > Senior Programmer / Analyst
> > University Of Pittsburgh

### Find the last positon of a character

For those who want to solve problems with a minimum
of sql knowledge or just what to minimize sql
code in general consider upcoming Rac utility
for s2k.

To find the last position of the colon in each string:

To get the new string:

from rac order by rd'

stevie
www.angelfire.com/ny4/rac/
Rac (relational application companion)
for s2k available soon).

### Find the last positon of a character

I told myself "quit relying on others to give you the ansswer" and came up
with the code below.  Below is the select statement to get my substring by
finding the last position of a character in a string.

select Journalname,
reverse(Journalname),reverse(Substring(reverse(JournalName),charindex(':',re
verse(JournalName))+1,200)) from Journals

Here is the string I tested:
Start String
----------------------
Abdominal imaging. (Abdom Imaging): 1996 1997 1998 1999 2000

String Reversed
----------------------
0002 9991 8991 7991 6991 :)gnigamI modbA( .gnigami lanimodbA

String I needed
-----------------------
Abdominal imaging. (Abdom Imaging)

--
Daniel Bogesdorfer
Senior Programmer / Analyst
University Of Pittsburgh

> For those who want to solve problems with a minimum
> of sql knowledge or just what to minimize sql
> code in general consider upcoming Rac utility
> for s2k.

> To find the last position of the colon in each string:

> To get the new string:

> exec rac

>                                    from rac order by rd'

> stevie
> www.angelfire.com/ny4/rac/
> Rac (relational application companion)
> for s2k available soon).

### Find the last positon of a character

Quote:> I told myself "quit relying on others to give you the ansswer" and came up
> with the code below.  Below is the select statement to get my substring by
> finding the last position of a character in a string.

I guess this means you won't be placing your order:).

stevie

Hi,

I'm trying to migrate some code using the OO4O objects of Oracle to
ADO.  In Oracle, the Dynaset has 4 methods to find data : FindFirst,
FindNext, FindPrevious and FindLast.  No simple Find method.

The code I have to migrate use those 4 methods and I'm not sure if I
understand right, but I think FindFirst find the first row in the
dynaset matching the criteria.  FindLast the last one forward.
FindNext, the next one starting at the current row and FindPrevious,
the previous starting at the current row.

I don't know how to implement it with ADO.  I know I can set an option
to backward or forward.  But is there an quivalent to FindLast or
FindFirst?