Hi all,

I have a table with windows filenames and paths in it, and I'm trying to get
all distinct paths up to the first backslash.
Then, I need to get the path up to the second slash, etc.
My table has 100,000 rows, so getting the DB to do it instead of the front
end would be a bonus...

The substr UDF will be alright to return the sections of the path, but
what's the best way to find where the blackslashes are?
I could do:
while(a < strlen(path)) DO
BEGIN
char = substr(path,a,a+1);
if(char = '\')THEN
BEGIN
/* We found a backslash! Do something */
END
ELSE a = a + 1;
END
But it makes coding very long winded in PL...


It would be so nice to have a UDF that you pass a variable, a character to
search for, and the numbered occurance you wanted, and it returnes the
position in the string of that occurance:
ie. to get all distinct paths up to the first backslash:
select distinct(substr(str_path,0,strpos(str_path,'\',1))) from tbl_files
order by 1;

To get all distinct paths up to the second backslash:
select distinct(substr(str_path,0,strpos(str_path,'\',2))) from tbl_files
order by 1;

To get all distinct paths between 1st and 2nd slashes:
select
distinct(substr(str_path,strpos(str_path,'/',1),strpos(str_path,'\',2)))
from tbl_files order by 1;

Anyone know of a UDF that'll do it?

Nige.