Resolving "fileid=xxx" field values to actual file paths

Following a previous article of mine, The dbo.tabs.IconFile field issue, I would like to share an easy way to resolve any field that holds values of the type "FileID=xxx" (where xxx is the primary key of a record in the Files table) into the actual file path. This is especially useful when you deal with the IconFile field in the Tabs table, as well as with fields of type Image in a User Defined Table, and probably in a lot more places too.

 

I have created a UDF (User Defined Function) which accepts a string value and looks whether it's of type "FileID=xxx". If it is, it constructs the full path (including the portal number) and returns that, otherwise it just returns the initial string with no modification at all. You can use this scalar-valued function inline, in your own SELECT statements, like this:

 

EXAMPLE

SELECT 
tabid, 
tabname, 
dbo.ResolveFileField(iconfile) 
FROM tabs 

 

CODE

CREATE FUNCTION 
dbo.ResolveFileField
(
    @file varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN
    
declare @retval varchar(1000)
declare @portalid int

if patindex ('FileID=%', @file) >0

begin

    declare @fileid int
    set @fileid = 
        convert(
                int, 
                replace
                    (
                    @file
                    , 'fileid='
                    , ''
                    )
                )   

    select 
        @retval = 
            'Portals/' 
            + convert
                    (
                      varchar(10)
                    , files.portalid
                    ) 
            + '/' 
            + folders.folderpath 
            + files.filename 
    from 
        files
    left outer join folders
    on files.folderid = folders.folderid
    where 
        files.fileid=@fileid
    
end

else

begin
    set @retval = @file
end

return @retval

END
GO