UFN_QUERYVIEW_GETEXPORTHEADERABBREVIATION

CREATE function dbo.[UFN_QUERYVIEW_GETEXPORTHEADERABBREVIATION]
(
  @NAME nvarchar(200)
)
returns nvarchar(200)
as begin
  declare @ABBREVIATION nvarchar(200) = '';

  declare @NAMEPART nvarchar(200);
  declare @ABBREVIATEDNAMEPART nvarchar(200);
  declare @SPACEINDEX integer;

  declare @DONE bit = 0;

  -- trim leading and trailing spaces, remove parentheses

  set @NAME = ltrim(rtrim(replace(replace(@NAME, '(', ''), ')', '')));

  if exists(select [ABBREVIATION] from dbo.[EXPORTHEADERABBREVIATION] where [NAME] = @NAME) begin
    -- if there is an entry in the EXPORTHEADERABBREVIATION table that matches the name in its entirety, use that

    select @ABBREVIATION = [ABBREVIATION] from dbo.[EXPORTHEADERABBREVIATION] where [NAME] = @NAME
  end else
    -- build an abbreviation from the name

    begin
      while @DONE = 0
        begin
          set @ABBREVIATEDNAMEPART = '';

          -- grab the first word from the name...

          set @SPACEINDEX = charindex(' ', @NAME);

          if @SPACEINDEX > 0 begin
            set @NAMEPART = left(@NAME, @SPACEINDEX - 1);
          end else begin
            set @NAMEPART = @NAME;
          end

          -- determine its abbreviation

          select @ABBREVIATEDNAMEPART = [ABBREVIATION] from dbo.[EXPORTHEADERABBREVIATION] where [NAME] = @NAMEPART;

          -- tack the result onto the abbreviation we're building

          if @ABBREVIATEDNAMEPART is null or @ABBREVIATEDNAMEPART = '' begin
            set @ABBREVIATION = @ABBREVIATION + @NAMEPART;
          end else begin
            set @ABBREVIATION = @ABBREVIATION + @ABBREVIATEDNAMEPART;
          end

          if @SPACEINDEX > 0 begin
            -- move on to the rest of the name (ltrim in case there's an extra space between words)

            set @NAME = ltrim(right(@NAME, len(@NAME) - @SPACEINDEX));
          end else begin
            -- we're done

            set @DONE = 1;
          end
        end
    end

  return @ABBREVIATION;
end