locked
Fetch element at particular position RRS feed

  • Question

  • What is the SQL syntax to find last directory name from a file path. For example I want to fetch the name of the last Directory i.e. CBBVF

    We need to fetch the whole string that is after last Directory_ 

    Input :/Directory_A/Directory_B/Directory_CBBVF/Folder1/Folder2

    Output: CBBVF

    I am using SQL Server

    Thursday, August 6, 2020 3:43 AM

All replies

  • SQL Server manage data, not file system. There is an undocumented XP named xp_dirtree, but undocumented = unsupported.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 6, 2020 6:51 AM
  • The syntax in this case is a combination of calls to substring and charindex. The reverse function may also come in handy. SQL Server is not exactly Python or Perl with a lot of great toolss for string manipulation. Then again, starting with SQL 2017, you can invoke Python from SQL Server (if you installed this support).

    I abstain from trying to answer the actual question, because the question is contradictive. You say that you want the last directory, and that would be Folder1 or maybe Folder2. But instead you want part of a directory name in the middle.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 6, 2020 7:46 AM
  • If you want to extract the word between the last ‘Directory_’ and next ‘/’, maybe you can adapt some previous suggestions, based on string or XML manipulation: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5e2300e-fe1d-44fb-b0ad-39ef69970f72.

    Thursday, August 6, 2020 8:08 AM
  • Hi cherry11111,

    I found a suggestion from a similar thread. Hope this could help you.

    select v.*, left(v.dir, charindex('/', v.dir) - 1) as dirname
    from (values ('/Directory_A/Directory_B/Directory_C/Folder1/Folder2') ) t(path) cross apply
         (values (stuff(t.path, 1, len(t.path) - charindex(reverse('Directory_'), reverse(t.path)) + 1, ''))) v(dir)

    Best regards,
    Cathy


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Friday, August 7, 2020 6:11 AM
  • Hi cherry11111,

    Any update? Did the replies could help you?

    Best regards,
    Cathy

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 10, 2020 6:51 AM