--SQL中字符串拆分方法
CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

CREATE FUNCTION [dbo].[GetTransformStringByName]
(
  @String NVARCHAR(128)
)
RETURNS NVARCHAR(512)
AS
BEGIN
  --将Name字段拆分
  SELECT * INTO #Temp from dbo.Split(@String);
  --关联字典表
  SELECT t1.Data, t2.name into #Temp2 from #Temp as t1
  left join t2 on t2.code = t1.Data;
  --构造返回值
  DECLARE @Result NVARCHAR(512);
  Set @Result = '';

  SELECT @Result = @Result + Data + '@' + name from #Temp2;

  RETURN @Result;
END

--如何使用？
select [dbo].GetTransformStringByName(name) from t1