【011】获取多语言表对应的扩展字段的显示名称

if(OBJECT_ID('fn_CombineDisplayName') is not null)
  drop function fn_CombineDisplayName
go

CREATE FUNCTION fn_CombineDisplayName
(
    @CombineName nvarchar(max), -- 多语言表的CombineName
    @Num int -- 第几个扩展字段，私有扩展段+50
)
RETURNS nvarchar(max)
AS
BEGIN
  declare @Result nvarchar(max)=''
    declare @sep varchar(6),@sepLen int,@indexS int
  set @sep='#@#'
  set @sepLen=Len(@sep)
  set @indexS=CHARINDEX(@sep, @CombineName)

  if(@Num=1)
  begin
    set @Result = substring(@CombineName, 1, @indexS - 1)
  end
  else
  begin 
    declare @i int
    set @i=2
    while(@i<@Num)
    begin
      set @indexS=CHARINDEX(@sep, @CombineName, @indexS + @sepLen)
      set @i+=1
    end

    set @indexS = @indexS + @sepLen
    set @Result = substring(@CombineName, @indexS, CHARINDEX(@sep, @CombineName, @indexS) - @indexS)
  end

  return @Result
END

-- 示例
-- declare @str nvarchar(max)='裁板#@#电建地产-云立方#@#研发类#@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@#1231314#@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@##@#'
-- select dbo.fn_CombineDisplayName(@str, 3)
GO