IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'BRIEF_FN_GetUNCPathFromFile'
      AND SO.[type] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[BRIEF_FN_GetUNCPathFromFile]
  END
GO

CREATE FUNCTION
       [dbo].[BRIEF_FN_GetUNCPathFromFile]
      (@strFileName     varchar(max))
RETURNS varchar(max)
AS
/************************************************************************
 *
 * Get the UNC Location of a file (provided the drive is stored in
 * the UNCAlias table)
 *
 * Copied From : KAAS_GetUNCPathFromFile
 * Modification History
 * Balamurugan.c     01-Jul-2022    Created
 ************************************************************************/
BEGIN

    DECLARE @strUNCFileName varchar(max)

    SET @strFileName = ISNULL(@strFileName, '')
    SET @strUNCFileName = @strFileName

    IF (SUBSTRING(@strUNCFileName, 2, 1) = ':')
      BEGIN
        SELECT @strUNCFileName = ISNULL(MAX(UNC.[UNC]) + SUBSTRING(@strUNCFileName, 3, LEN(@strUNCFileName) - 2), @strUNCFileName)
          FROM [dbo].[UNCAlias] UNC
         WHERE SUBSTRING(@strUNCFileName, 1, 1) = UNC.[Drive]

        SET @strUNCFileName = LEFT(@strUNCFileName, 2) + REPLACE(RIGHT(@strUNCFileName, LEN(@strUNCFileName) - 2), '\\', '\')
      END

    RETURN @strUNCFileName
END

GO