SQL query to pull the document revision from a file path-CodePudding

I have a document table that stores a document name and the file path. The document revision that I need is embedded in the file path. The revision is the last character, (or last two characters), before the file extension and preceded with a dash. The only caveat is that a document that has not been revised also uses a dash as a revision place holder. I need to be able to extract that dash, as well.

CodePudding user response：

Here is a possible solution you can try. It's often easier to work with a reversed version of the string in this situation since then you always know you are starting with the file extension and so are looking for the first . and - in the string.

with r as (
    select urlLocation, Reverse(urlLocation) r
    from t
),
p as (
    select *,
    CharIndex ('.',r) 1 s, 
    IsNull(NullIf(CharIndex ('-',r)-charindex ('.',r)-1,0),1) l
    from r
)
select urlLocation, Reverse(Substring(r,s,l))
from p

Demo DB<>Fiddle