Removing a set of string from a string

[Sanjivi]

Hi,

I have a table name DS_MEDIA_MAPPING.
The column 'MEDIA_PATH_PREFIX' contains paths like below:

C:\Documents and Settings\admin\My Documents\media\ABC001\
C:\Documents and Settings\admin\My Documents\media\SOS001\DESK\
C:\Documents and Settings\admin\MEDIA\FTP001\READ_ME.txt
.
.
.

Here, I have to get the folder name after the folder name media. In the above example, I need :
ABC001
SOS001
FTP001

I ran the following query :

Select (Substring(MEDIA_PATH_PREFIX, PATINDEX('%\media\%', MEDIA_PATH_PREFIX) + 7, LEN(MEDIA_PATH_PREFIX)))
FROM DS_MEDIA_MAPPING


This query gives me all the characters after '\media\'.

Please help me in removing all the characters after:
ABC001
SOS001
FTP001

[skhanal]

Use REVERSE function to reverse the string and find first /, then subtract position from length of the script to get position of /, then use substring to extract the string you want.

[Sanjivi]

I tried but am not getting desired one.
Could anyone please write it for me?

[skhanal]

I have not tested it, but it should be something like this

Select (Substring(MEDIA_PATH_PREFIX, PATINDEX('%\media\%', MEDIA_PATH_PREFIX) + 7, LEN(MEDIA_PATH_PREFIX)-patindex('\',reverse(media_path_prefix)))
FROM DS_MEDIA_MAPPING