如何在 SQL Server 中删除复杂子查询选择语句的最后一个字符

Question

我有这个查询，现在在这种情况下如何删除选择语句的最后一个字符？

SELECT  ...,
    ...,
    ,CASE sfo.[Type] WHEN 'true' THEN sr.[Note] WHEN 'false' THEN 
    (
        SELECT Options + ', ' AS [text()] FROM Table1 sa JOIN SurveyResult surRes ON sa.Id=surRes.Table1Id WHERE sa.IsActive=1 AND Table4Id = 4 AND surRes.FormIdentifier=sr.[FormIdentifier] FOR XML PATH ('')
--This select Option returns a record like this:
--'a,b,c,' or 'Male,Female,'
--How do I remove the last character for this instance.
--I saw examples of using substring on the internet but it requires using a variable.  Something I can't afford using in this scenario.

    )  ELSE sa.Options END AS Answer
    FROM [dbo].[SurveyResult] sr
           LEFT JOIN [dbo].[Table1] sa on sr.[Table1Id] = sa.Id
           INNER JOIN [dbo].[Table2] sq ON sr.Table2Id = sq.Id
           INNER JOIN [dbo].[Table3] si ON sq.Table3Id = si.Id
           INNER JOIN [dbo].[Table4] sfo ON sq.Table4Id = sfo.Id
           INNER JOIN [dbo].[Table5] u ON sr.[Table5Id] = u.Id
           INNER JOIN [dbo].[Table6] a ON si.Table6Id = a.Id
           INNER JOIN [dbo].[Table7] l ON si.Table7Id = l.Id
    WHERE si.IsActive = 1
           ORDER BY sr.Id

我试过用这个

SELECT REVERSE(STUFF(REVERSE(SELECT Options + ', ' AS [text()] FROM Table1 sa JOIN SurveyResult surRes ON sa.Id=surRes.Table1Id WHERE sa.IsActive=1 AND Table4Id = 4 AND surRes.FormIdentifier=sr.[FormIdentifier] FOR XML PATH ('')), 1, 1, ''))

不行。

Answer 1

而不是像male, female,那样将逗号放在末尾，而是将其放在开头(例如,male,female(

然后，您可以轻松使用STUFF删除第一个字符：

SELECT STUFF(',male,female',1,1,'');

显然，分隔字符串将是您的列值。

Answer 2

另一种方法(也照顾NULL( -

DECLARE @string NVARCHAR(100) = 'Male,Female,'
SELECT LEFT(@string, NULLIF(LEN(@string)-1,-1))

Answer 3

>请尝试SQL服务器的代码

declare @abc nvarchar(1000)='a,b,c,'
select LEFT(@abc, LEN(@abc) - 1)