I am trying to break down file paths into columns for it's different directory and file name parts. I am by no means an expert so all feedback apreciated:
(It works fine up to level 6 or 7)
CREATE TABLE T1(FilDat, FilTim, PathFileName, DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8, FilNam);
INSERT INTO T1 VALUES('1993-06-17', '09:54:59', 'DirLevOne\LevTwo\Three\lpk0924.mne', '', '', '', '', '', '', '', '', '', '');
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6,
substr(DirLev7, 1, pos-1) AS DirLev7,
substr(DirLev7, pos+1) AS DirLev8
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7,
instr(DirLev7, '\') AS pos
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5,
substr(DirLev6, 1, pos-1) AS DirLev6,
substr(DirLev6, pos+1) AS DirLev7
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6,
instr(DirLev6, '\') AS pos
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4,
substr(DirLev5, 1, pos-1) AS DirLev5,
substr(DirLev5, pos+1) AS DirLev6
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5,
instr(DirLev5, '\') AS pos
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3,
substr(DirLev4, 1, pos-1) AS DirLev4,
substr(DirLev4, pos+1) AS DirLev5
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4,
instr(DirLev4, '\') AS pos
FROM (
SELECT DirLev0, DirLev1, DirLev2,
substr(DirLev3, 1, pos-1) AS DirLev3,
substr(DirLev3, pos+1) AS DirLev4
FROM (
SELECT DirLev0, DirLev1, DirLev2, DirLev3,
instr(DirLev3, '\') AS pos
FROM (
SELECT DirLev0, DirLev1,
substr(DirLev2, 1, pos-1) AS DirLev2,
substr(DirLev2, pos+1) AS DirLev3
FROM (
SELECT DirLev0, DirLev1, DirLev2,
instr(DirLev2, '\') AS pos
FROM (
SELECT DirLev0,
substr(DirLev2, 1, pos-1) AS DirLev1,
substr(DirLev2, pos+1) AS DirLev2
FROM (
SELECT DirLev0,
DirLev2,
instr(DirLev2, '\') AS pos
FROM (
SELECT substr(PathFileName, 1, pos-1) AS DirLev0,
substr(PathFileName, pos+1) AS DirLev2
FROM (
SELECT *,
instr(PathFileName,'\') AS pos
FROM T1
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)