{"id":83856,"date":"2019-04-13T16:20:50","date_gmt":"2019-04-13T16:20:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83856"},"modified":"2019-08-15T13:43:45","modified_gmt":"2019-08-15T13:43:45","slug":"extract-the-path-from-filename-in-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/extract-the-path-from-filename-in-sql\/","title":{"rendered":"Extract the Path from Filename in SQL"},"content":{"rendered":"<p>While I was preparing an article, I faced the challenge to extract the path from a\u00a0filename in SQL Server. It&#8217;s an interesting challenge with many possible uses, so I decided to create a function to solve this problem.<\/p>\n<h3>\nThe Main Expression<\/h3>\n<p>First, let&#8217;s understand the main expression to solve the problem. The path is everything before the last backslash (&#8216;\\&#8217;) in the full file name. So, we can proceed this way:<\/p>\n<ul>\n<li><strong>Reverse<\/strong> the full filename<\/li>\n<li>Get the <strong>CharIndex<\/strong> of the first backslash (&#8216;\\&#8217;)<\/li>\n<li>Get the <strong>len<\/strong>gth of the full filename subtracted by the <strong>CharIndex<\/strong> of the first backslash<\/li>\n<li>From the <strong>left<\/strong> of the full file name, get the number of characters calculated by the previous formula<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3>Creating the function<\/h3>\n<p style=\"padding-left: 30px\">The next step is creating a function to solve this problem, so we don&#8217;t need to repeat the expression all the time.<\/p>\n<p style=\"padding-left: 30px\">The function will be like this:<\/p>\n<div style=\"padding-left: 30px\"><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">CREATE<\/span>\u00a0<span style=\"color: blue\">FUNCTION<\/span>\u00a0<span style=\"color: maroon\">dbo<\/span><span style=\"color: silver\">.<\/span><span style=\"color: #ff0080;font-weight: bold\">Pathfromfullname<\/span>\u00a0<span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span> <br \/>\n<span style=\"color: maroon\">returns<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span> <br \/>\n<span style=\"color: blue\">AS<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">BEGIN<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">DECLARE<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">SELECT<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span>\u00a0<span style=\"color: silver\">=<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">LEFT<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Len<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: maroon\">)<\/span>\u00a0<span style=\"color: silver\">&#8211;<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Charindex<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: red\">&#8216;\\&#8217;<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Reverse<\/span><span style=\"color: maroon\">(<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">RETURN<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">END<\/span>\u00a0 <\/span><\/div>\n<p style=\"padding-left: 30px\">\nIn order to test the function, we can execute the following instruction:<\/p>\n<div style=\"padding-left: 30px\"><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">SELECT<\/span> <br \/>\n<span style=\"color: maroon\">dbo<\/span><span style=\"color: silver\">.<\/span><span style=\"color: #ff0080;font-weight: bold\">Pathfromfullname<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: red\">&#8216;C:\\Program\u00a0Files\\Microsoft\u00a0SQL\u00a0Server\\MSSQL15.SQL2\\MSSQL\\Log\\system_health_0_131996396680890000.xel&#8217;<\/span><span style=\"color: maroon\">)<\/span>\u00a0 <\/span><\/div>\n<h3>Increasing the Safety<\/h3>\n<p style=\"padding-left: 30px\">If the parameter has no backslash, the function will fail. A simple check can increase the safety of the function, but we can also turn this into another function that may be used in many places:<\/p>\n<div style=\"padding-left: 30px\"><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">CREATE<\/span>\u00a0<span style=\"color: blue\">FUNCTION<\/span>\u00a0<span style=\"color: maroon\">dbo<\/span><span style=\"color: silver\">.<\/span><span style=\"color: #ff0080;font-weight: bold\">Isfullpath<\/span>\u00a0<span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span> <br \/>\n<span style=\"color: maroon\">returns<\/span>\u00a0<span style=\"color: black;font-style: italic\">BIT<\/span> <br \/>\n<span style=\"color: blue\">AS<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">BEGIN<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">DECLARE<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span>\u00a0<span style=\"color: black;font-style: italic\">BIT<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">IF<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Charindex<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: red\">&#8216;\\&#8217;<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: maroon\">)<\/span>\u00a0<span style=\"color: silver\">=<\/span>\u00a0<span style=\"color: black\">0<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">SET<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span><span style=\"color: silver\">=<\/span><span style=\"color: black\">0<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">ELSE<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">SET<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span><span style=\"color: silver\">=<\/span><span style=\"color: black\">1<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">RETURN<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">END<\/span>\u00a0 <\/span><\/div>\n<p style=\"padding-left: 30px\">Let&#8217;s fix the PathFromFullName function:<\/p>\n<div style=\"padding-left: 30px\"><span style=\"font-family: Courier New;font-size: 10pt\"> <span style=\"color: blue\">ALTER<\/span>\u00a0<span style=\"color: blue\">FUNCTION<\/span>\u00a0<span style=\"color: maroon\">dbo<\/span><span style=\"color: silver\">.<\/span><span style=\"color: #ff0080;font-weight: bold\">Pathfromfullname<\/span>\u00a0<span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span> <br \/>\n<span style=\"color: maroon\">returns<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span> <br \/>\n<span style=\"color: blue\">AS<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">BEGIN<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">DECLARE<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span>\u00a0<span style=\"color: black;font-style: italic\">VARCHAR<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: black\">500<\/span><span style=\"color: maroon\">)<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">IF<\/span>\u00a0<span style=\"color: maroon\">(<\/span>\u00a0<span style=\"color: maroon\">dbo<\/span><span style=\"color: silver\">.<\/span><span style=\"color: #ff0080;font-weight: bold\">Isfullpath<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: maroon\">)<\/span>\u00a0<span style=\"color: silver\">=<\/span>\u00a0<span style=\"color: black\">1<\/span>\u00a0<span style=\"color: maroon\">)<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">SELECT<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span>\u00a0<span style=\"color: silver\">=<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">LEFT<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Len<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: #8000ff\">@FullName<\/span><span style=\"color: maroon\">)<\/span>\u00a0<span style=\"color: silver\">&#8211;<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: fuchsia;font-style: italic\">Charindex<\/span><span style=\"color: maroon\">(<\/span><span style=\"color: red\">&#8216;\\&#8217;<\/span><span style=\"color: silver\">,<\/span>\u00a0<span style=\"color: fuchsia;font-style: italic\">Reverse<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: maroon\">(<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: #8000ff\">@FullName<\/span> <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span><span style=\"color: maroon\">)<\/span> <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color: blue\">RETURN<\/span>\u00a0<span style=\"color: #8000ff\">@result<\/span> <br \/>\n\u00a0\u00a0<span style=\"color: blue\">END<\/span>\u00a0 <\/span><\/div>\n<p style=\"padding-left: 30px\">Now if the parameter is not a full filename the result will be null<\/p>\n","protected":false},"excerpt":{"rendered":"<p>While I was preparing an article, I faced the challenge to extract the path from a\u00a0filename in SQL Server. It&#8217;s an interesting challenge with many possible uses, so I decided to create a function to solve this problem. The Main Expression First, let&#8217;s understand the main expression to solve the problem. The path is everything&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4658,4816],"coauthors":[6810],"class_list":["post-83856","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-functions","tag-sql-server-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83856","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83856"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83856\/revisions"}],"predecessor-version":[{"id":83858,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83856\/revisions\/83858"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83856"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83856"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83856"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83856"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}