SQL query get only parent directory

SELECT *
FROM directory
WHERE status_id = 10 AND workspace_id = 1

Above is my query and database table.

dir_name = directory name

dir_path = directory path

For example, BbB is a nested directory under AaA.

How can I write a query for this when I call the above data, instead of all 5 rows display, only show 1 row, the parent directory. In this case, only id = 13 data will show because it was the parent directory for all of the data.

Answer

Here is a idea using length and replace to indicate the layer of a folder/file, so not just finding the parent folder/file you can find what ever layer you want assuming your dir_path will always formmat in aaabbbcccddd

select tb.*,len(dir_path) - len(replace(dir_path,'','')) as layer 
from [tablename] tb
where len(dir_path) - len(replace(dir_path,'','')) = 1

and here is db<>fiddle