Any SQL equivalent of InStrRev()?

In VB, there's a string function called InStrRev() which returns the
position of an occurrence of one string within another, from the end of
string.
I have a column containing data such as "/uploads/encrypted/design1.doc". I
want to be able to extract the pure filename from this column (e.g. returns
"design1.doc" from the above example).
In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
and then extract the rest of the string from that position.
Is there a similar way to do this in SQL script?
Thank you.DId you try something using CHARINDEX(REVERSE())?
SELECT CHARINDEX('x', 'foobarxgoo')
SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
"Bob" <noreply@.nospam.com> wrote in message
news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
> In VB, there's a string function called InStrRev() which returns the
> position of an occurrence of one string within another, from the end of
> string.
> I have a column containing data such as "/uploads/encrypted/design1.doc".
> I want to be able to extract the pure filename from this column (e.g.
> returns "design1.doc" from the above example).
> In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
> and then extract the rest of the string from that position.
> Is there a similar way to do this in SQL script?
> Thank you.
>|||Hi Aaron,

> SELECT CHARINDEX('x', 'foobarxgoo')
returns 7

> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
returns 4
The only way I can find this useful is if I REVERSE the string (i.e.
"/uploads/encrypted/design1.doc" to "cod.1ngised/detpyrcne/sdaolpu/"), then
find the first instance of a slash "/". Truncate the rest of the string.
That would get me "cod.1ngised". And finally, REVERSE again to get
"design1.doc".
But that sounds like a lot of calls and could slow down performance!
What do you think?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23CZHh883FHA.2196@.tk2msftngp13.phx.gbl...
> DId you try something using CHARINDEX(REVERSE())?
> SELECT CHARINDEX('x', 'foobarxgoo')
> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
>
> "Bob" <noreply@.nospam.com> wrote in message
> news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
>|||You can directly use the expression:
RIGHT( @.s, CHARINDEX( '/', REVERSE( @.s ) ) - 1 )
Obviously, any nested string manipulation functions done over a large
dataset is going to impact performance ( VB is no different either ). In
many cases it might be negligible. If it is a crucial issue, rather than
putting the entire path in a single column, consider using a separate column
for the file name.
Anith