去掉sql查询结果中的最后一个逗号
CREATE FUNCTION dbo.f_position(@ClassID BIGINT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @str VARCHAR(8000) SET @str = '' SELECT @str = @str + (case WHEN (dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') = 'Student') THEN '.' ELSE ',' + ISNULL((dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA')),'') + '_' + isnull([StevenhMembers].[MemberName] ,'') END ) FROM [ReadModel].[StevenhMembers] INNER JOIN [ReadModel].[StevenhClass] ON ReadModel.StevenhClass.ClassID = ReadModel.StevenhMembers.ClassID WHERE ([StevenhClass].[ClassID]=@ClassID OR @ClassID IS NULL) AND dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') in ('Teacher', 'Monitor','Vice Monitor','Student') ORDER BY Position RETURN ISNULL(STUFF(REPLACE(@str,'.',''), 1, 1, ''),'') --substring(LTRIM(REPLACE(@str,'.','')),1,len(RTRIM(@str))-1) --去除最后一个逗号 END go
代码第15行,ISNULL(STUFF(REPLACE(@str,
'.'
,
''
),
1
,
1
,
''
),
''
)
这里用了stuff函数和replace函数,此处的逗号设置在 字符串开头,所以start位置是1,逗号的length为1,用此方法就可以去除开头的逗号了!
因为用到了replace函数,原因是,不需要显示student的字符串,所以在做判断的时候,将@str设置为一个‘.’,可能会很麻烦的样子,但后面用replace就可以去除这些‘.’了。
转载于:https://blog.51cto.com/asheng0321/1404580