Reports

This can be achieved in sqlite with some "creative" use of the right-trim and string replacement functions.

# Table 'files' contain the fullname in the 'file' column
# .mode is set to line for readability

sqlite> SELECT
  file AS fullname,
  RTRIM(file, REPLACE(file, '\', '')) AS parentpath,
  REPLACE(file, 
    RTRIM(file, REPLACE(file, '\', '')),
    '') AS filename
FROM files

  fullname = C:\Users\Public\Music\iTunes\iTunes Media\Music\Nirvana\Last Concert In Japan\16 Smells Like Teen Spirit.mp3
parentpath = C:\Users\Public\Music\iTunes\iTunes Media\Music\Nirvana\Last Concert In Japan\
  filename = 16 Smells Like Teen Spirit.mp3

Why this works? We typically think of rtrim as a function that removes spaces or a specified trim string from the end of the source string. Thus rtrim('abc','c') = ab.

However, it actually is working on a match of any and all characters in any order at the end of both source and trim strings. Thus rtrim('abc','dec') = ab and rtrim('abc','cb') = a.

Knowing that, we first replace the path separators in the file string and rtrim it against the original string. Since only the filename portion matches, it is removed -- giving us the parent path we seek.

We then we then replace the parent path from the original string -- resulting in the filename we seek.

79305544