Well, that's a beautiful code tree!
Aside from the bug/fixing/problem, may I suggest a method slightly more sane:
This script makes the same table as you proposed, but I added some values that would be problematic and then some sections to do what you intended.
Only Section 3 is the one doing the real work, but the prior two sections are included to show the steps and what their data looks like.
- The first section only parse and pulls apart the path.
- The next section then amalgamates the parsed path bits into a kind of pivot-table to do what (I hope) you intended.
- The last section simply UPDATEs that into your original table and then displays its content.
PS: This is bad for so many reasons, this example shows HOW, but I'm not promoting this as a good thing to do. Path parsing should really be left to the end-system code.
```
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- SQLite version 3.30.1 [ Release: 2019-10-10 ] on SQLitespeed version 2.1.3.11.
-- Script Items: 6 Parameter Count: 0
-- 2020-07-06 16:28:21.511 | [Info] Script Initialized, Started executing...
-- ================================================================================================
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', '', '', '', '', '', '', '', '', '', '')
,('1993-06-18', '09:50:00', 'DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne', '', '', '', '', '', '', '', '', '', '')
,('1998-05-16', '09:51:01', 'DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne', '', '', '', '', '', '', '', '', '', '')
;
-- SECTION 1 - Parsing
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
)
SELECT t.FilDat, t.FilTim, t.PathFileName, cr.d, cr.r AS colPathSect FROM T1 AS t, pathrec AS cr
WHERE t.rowid = cr.i AND cr.r <> ''
ORDER BY t.rowid, cr.d
;
-- FilDat |FilTim |PathFileName | d |colPathSect
-- ------------|----------|----------------------------------------------------------|---|-------------
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 0 |DirLevOne
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 1 |LevTwo
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 2 |Three
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 3 |lpk0924.mne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 0 |DirLevOne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 1 |LevTwo
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 2 |Three
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 3 |Four
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 4 |Five
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 5 |lpk0923.mne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 0 |DirLevOne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 1 |LevTwo
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 2 |Three
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 3 |Four
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 4 |Five
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 5 |L6
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 6 |L7
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 7 |L8
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 8 |L9
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 9 |lpk0925.mne
-- SECTION 2 - Pivot
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
), paths AS (
SELECT cr.i, cr.d, cr.r
FROM pathrec AS cr
WHERE cr.r <> ''
GROUP BY cr.i, cr.d
), pathexp(i, DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8, DirOver) AS (
SELECT i,
MAX(CASE WHEN d = 0 THEN r ELSE '' END),
MAX(CASE WHEN d = 1 THEN r ELSE '' END),
MAX(CASE WHEN d = 2 THEN r ELSE '' END),
MAX(CASE WHEN d = 3 THEN r ELSE '' END),
MAX(CASE WHEN d = 4 THEN r ELSE '' END),
MAX(CASE WHEN d = 5 THEN r ELSE '' END),
MAX(CASE WHEN d = 6 THEN r ELSE '' END),
MAX(CASE WHEN d = 7 THEN r ELSE '' END),
MAX(CASE WHEN d = 8 THEN r ELSE '' END),
GROUP_CONCAT(CASE WHEN d > 8 THEN r ELSE '' END,'')
FROM paths
GROUP BY i
)
SELECT * FROM pathexp
;
-- i |DirLev0 |DirLev1 |DirLev2|DirLev3 |DirLev4|DirLev5 |DirLev6|DirLev7|DirLev8|DirOver
-- ------------|-----------|--------|-------|-------------|-------|-------------|-------|-------|-------|-------------
-- 1 |DirLevOne |LevTwo |Three |lpk0924.mne | | | | | |
-- 2 |DirLevOne |LevTwo |Three |Four |Five |lpk0923.mne | | | |
-- 3 |DirLevOne |LevTwo |Three |Four |Five |L6 | L7 | L8 | L9 |lpk0925.mne
-- SECTION 3 - Populate Table
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
), paths AS (
SELECT cr.i, cr.d, cr.r
FROM pathrec AS cr
WHERE cr.r <> ''
GROUP BY cr.i, cr.d
), pathexp(i, D0, D1, D2, D3, D4, D5, D6, D7, D8, DEx) AS (
SELECT i,
MAX(CASE WHEN d = 0 THEN r ELSE '' END),
MAX(CASE WHEN d = 1 THEN r ELSE '' END),
MAX(CASE WHEN d = 2 THEN r ELSE '' END),
MAX(CASE WHEN d = 3 THEN r ELSE '' END),
MAX(CASE WHEN d = 4 THEN r ELSE '' END),
MAX(CASE WHEN d = 5 THEN r ELSE '' END),
MAX(CASE WHEN d = 6 THEN r ELSE '' END),
MAX(CASE WHEN d = 7 THEN r ELSE '' END),
MAX(CASE WHEN d = 8 THEN r ELSE '' END),
GROUP_CONCAT(CASE WHEN d > 8 THEN r ELSE '' END,'')
FROM paths
GROUP BY i
)
UPDATE T1
SET (DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8) =
(SELECT D0, D1, D2, D3, D4, D5, D6, D7, D8 FROM pathexp WHERE pathexp.i = T1.rowid),
FilNam = (SELECT CASE
WHEN DEx!= '' THEN DEx
WHEN D8 != '' THEN D8
WHEN D7 != '' THEN D7
WHEN D6 != '' THEN D6
WHEN D5 != '' THEN D5
WHEN D4 != '' THEN D4
WHEN D3 != '' THEN D3
WHEN D2 != '' THEN D2
WHEN D1 != '' THEN D1
ELSE D0
END FROM pathexp WHERE pathexp.i = T1.rowid)
WHERE 1
;
SELECT *
FROM T1
;
-- FilDat |FilTim |PathFileName |DirLev0 |DirLev1|DirLev2|DirLev3 |DirLev4|DirLev5 |DirLev6|DirLev7|DirLev8|FilNam
-- -----------|---------|---------------------------------------------------------|----------|-------|-------|-----------|-------|-----------|-------|-------|-------|-------------
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne |DirLevOne |LevTwo |Three |lpk0924.mne| | | | | |lpk0924.mne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne |DirLevOne |LevTwo |Three |Four |Five |lpk0923.mne| | | |lpk0923.mne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne |DirLevOne |LevTwo |Three |Four |Five |L6 | L7 | L8 | L9 |lpk0925.mne
-- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.035s
-- Total Script Query Time: 0d 00h 00m and 00.003s
-- Total Database Rows Changed: 6
-- Total Virtual-Machine Steps: 10045
-- Last executed Item Index: 6
-- Last Script Error:
-- ------------------------------------------------------------------------------------------------
-- 2020-07-06 16:28:21.530 | [Success] Script Success.
-- ================================================================================================
```
Note: The original table does not leave a method for handling overflow (paths longer than 9 sections) but the parser can add it as the un-parsed remaining path (see second step)... Which is one of the reasons this is a bad idea in a DB.
At least it was fun to do :)
Ryan