|
Home > Archive > Microsoft SQL Server forum > November 2005 > Splitting a filename out of a filepath
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Splitting a filename out of a filepath
|
|
| starritt@gmail.com 2005-11-18, 1:23 pm |
|
I have to create a view where the filename is seperated from the path
to the file. Examples of the data include:
m:\images\big\myimg.jpg
m:\images\medium\myi
mg.jpg
z:\media\images\high
qual\myimg.jpg
Is there a function that will return the position in a string of the
last \ ? With this I can easily use substring or similar to pull and
push this stuff into the fields I want. If this function does not
exist how do I move forward?
| |
| ZeldorBlat 2005-11-18, 1:23 pm |
| I don't think they have a CHARINDEX that searches from the end of the
string. If you want to know the last position of a character within a
string, you can abuse the reverse() function like this:
len(filepath) - charindex('/', reverse(filepath)) + 1
That gives you the position of the last '/' in the string. Note that
this only works if you're searching for a single character (that is, it
won't work if you replace the '/' with some arbitrary string with
length > 1). Also, if there is no '/' in the string, this guy will
return len(filename) + 1 as opposed to the normal charindex() which
returns 0 if the needle isn't found.
| |
| starritt@gmail.com 2005-11-18, 8:24 pm |
| Thank you sir -- that should work nicely.
J
| |
| Terry Kreft 2005-11-21, 7:23 am |
| If you apply reverse to the first parameter in the charindex you could
search for multiple chars as well.
e.g.
declare
@search varchar(255),
@find varchar(255)
SELECT
@search = 'm:\images\big\myimg
.jpg',
@find = ''
SELECT
len(@search) - charindex(reverse(@f
ind), reverse(@search)) + 1 ,
LEFT(@search, len(@search) - charindex(reverse(@f
ind), reverse(@search)) +
1)
Returns
----------- -------------
14 m:\images\big\
declare
@search varchar(255),
@find varchar(255)
SELECT
@search = 'm:\images\big\myimg
.jpg',
@find = 'images'
SELECT
len(@search) - charindex(reverse(@f
ind), reverse(@search)) + 1 ,
LEFT(@search, len(@search) - charindex(reverse(@f
ind), reverse(@search)) +
1)
Returns
----------- --------
10 m:\images\
--
Terry Kreft
"ZeldorBlat" <zeldorblat@gmail.com> wrote in message
news:1132341746.949663.317500@g44g2000cwa.googlegroups.com...
>I don't think they have a CHARINDEX that searches from the end of the
> string. If you want to know the last position of a character within a
> string, you can abuse the reverse() function like this:
>
> len(filepath) - charindex('/', reverse(filepath)) + 1
>
> That gives you the position of the last '/' in the string. Note that
> this only works if you're searching for a single character (that is, it
> won't work if you replace the '/' with some arbitrary string with
> length > 1). Also, if there is no '/' in the string, this guy will
> return len(filename) + 1 as opposed to the normal charindex() which
> returns 0 if the needle isn't found.
>
| |
| ZeldorBlat 2005-11-21, 11:23 am |
| >If you apply reverse to the first parameter in the charindex you could
>search for multiple chars as well.
Good point...I hadn't thought of that.
| |
| Terry Kreft 2005-11-21, 11:23 am |
| Wll, I didn't think of using reverse in the frst place so your still ahead
of me on this one <g>.
--
Terry Kreft
"ZeldorBlat" <zeldorblat@gmail.com> wrote in message
news:1132586940.573948.135490@g14g2000cwa.googlegroups.com...
>
> Good point...I hadn't thought of that.
>
|
|
|
|
|