Im trying to create an EPDM list from an SQL query based on a particular folder name starting with XXX. Can anyone shed any light on which table I would need to query to get this as I cant seem to find it when I browse the tables.
Hello Scott, look in the dbo.Projects table, if you query this, it will give you a list of all folders in the vault.
Great thanks that worked a treat, is there any way to get "just" the 3rd part of the path for example currently my query will return the below
But all I want is the Folder C section. I know you can do a character count but this could change
Hi Scott, good question, unfortunately I'm not the best in SQL queries, perhaps some gurus like Tim Webb or Charley Saint maybe able to help?
If you're setup is like many others, your folder naming is fairly consistent which means you could use a string function. Something like this:
select left(path, 18) from projects
Where 18 is the number of characters from the left of the string before it returns a value.
Steve Ostrovsky is right on! Here's another thing to consider, wildcards.
use <put your vault db name here>
declare @FolderPath nvarchar(260)
set @FolderPath = '\%CO%' /* This is a folder name with wildcard % to search for */
select path from projects p
where p.Path like @FolderPath
Here are the results. Notice how the wildcard works. Be careful when using these, they can be good but can also return far more than you expected.
Hope this helps,
Believe in The Q!
Thanks for the mention Prasad!
I'd just split the string on the backslash.
Retrieving data ...