Splitting directory and file name

[Paul]

Hi

I have a column in a table that stores a directory and filename e.g.

"C:\Program Files\A Directory\File1.file"

I need to use only SQL (not T-SQL) in order to obtain just the actual
filename minus the file extension e.g.

"File1".

I do not believe that I can do this using CHARINDEX as that starts at
the beginning of the string and I need to start at the end in order to
strip out the directory.

Note also that a file extension may not even exist so this needs to be
accounted for. For anybody that uses Oracle I have written the
following:

SELECT SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 1,
DECODE(INSTR(SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 2), '.', 1 ), 0,

250, INSTR(SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 2), '.', 1 )))
FROM TABLE_1;

I am trying to come up with an equivalent but cannot find an equivalent
to the INSTR function which you can tell to start at the end of the
string.

Can anybody help?

Thanks in advance,

Paul

[Razvan Socol]

Hello, Paul

You can use something like this:

SELECT LEFT(column1,LEN(column1)-CHARINDEX('\',REVERSE(column1))+1),
SUBSTRING(column1,LEN(column1)-CHARINDEX('\',REVERSE(column1))+2,250)
FROM table_1

Razvan

[Andres Pineda]

Try this

SELECT LEFT(column1,LEN(column1)-CHARINDEX('\',REVERSE(column1))+1),
SUBSTRING(column1,LEN(column1)-CHARINDEX('\',REVERSE(column1))+2,LEN(SUBSTRING(column1,LEN(column1)-CHARINDEX('\',REVERSE(column1))+2,250))-CHARINDEX('.',REVERSE(column1)))
FROM table1


This will give you the file name without extension.-


Aj.-

On 2/16/06, Razvan Socol < rso...@gmail.com> wrote:

Hello, Paul

You can use something like this: andres...@infotel.com.do