无法删除第一个逗号字符串中的SQL Server

代码工作,除了精

SELECT DISTINCT isnull(@FEATURE + ', ', ',') + Feature FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] as rf2 where (rf2.roomid = rf.roomid) for xml path('')),

这是给我的问题。 我不知道如何摆脱第一个逗号。 我试图子,但它删除所有逗号(我猜的,因为它是在一个select语句)。 我甚至尝试COALESCE(@FEATURE +',','')+功能,但我结束了相同的结果,或者没有逗号或在年底或在开始一个额外的逗号。

DECLARE @FEATURE VARCHAR(500) -- INSERT INTO BookableRooms IF NOT EXISTS ROOM NUMBER INSERT INTO dbo.BookableRooms_test (FK_Building_code, Room_num,Other_room_name, Capacity_from, Capacity_to, Accessiblity,AV_book_separate,Flooring_type,Features, Food_drink_allowed, Alcohol_allowed,Internet, [Owner],FK_BookingContact_ID, LCD_projector,Computer,FK_SpaceManager_code,Last_updated_date,Last_updated_by,SpecialFlag) SELECT DISTINCT CASE WHEN rf.[Building Reference] is null or rf.[Building Reference] = '' THEN 'HH' ELSE rf.[Building Reference] END, [ROOMID],[Description], (SELECT MIN(rf2.CAPACITY) FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] AS rf2 WHERE rf2.ROOMID = rf.ROOMID), (SELECT MAX(rf2.CAPACITY) FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] AS rf2 WHERE rf2.ROOMID = rf.ROOMID), dbo.iszero([Wheelchair Accessible]), dbo.iszero(rf.[Separate AV]),dbo.isflat(rf.[Flat Floor]), ( SELECT DISTINCT isnull(@FEATURE + ', ', ',') + Feature FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] as rf2 where (rf2.roomid = rf.roomid) for xml path('')), dbo.iszero(rf.[Food and Drink]),dbo.iszero(rf.[Alcohol]),dbo.iszero(rf.[Room Internet]), 'HH',5, dbo.iszero(rf.[Digital Projector]),dbo.iszero(rf.[Computer]),'HH',GetDate(),'System',dbo.iszero(rf.[Restricted]) FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] as rf where NOT EXISTS( SELECT 1 FROM dbo.BookableRooms_test b WHERE b.Room_num = rf.ROOMID);

--------------解决方案-------------

使用的东西,但在子查询的外侧:

stuff ((SELECT DISTINCT ',' + Feature
FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] as rf2
WHERE (rf2.roomid = rf.roomid)
for xml path('')), 1, 1, '')

使用STUFF函数删除前导逗号。

SELECT DISTINCT STUFF(',' + Feature
FROM [128.100.194.219, 1435].[EMSData].[dbo].[vOSLRoomFeatures] as rf2 where (rf2.roomid = rf.roomid)
for xml path('')),1,1,''),

我不知道你想达到什么目的。 如果我的猜测是错误的,它会很好的,如果你告诉我们什么样的结果,你在什么情况下想到。

尝试这个

CASE WHEN @FEATURE IS NULL THEN NULL ELSE @FEATURE + ', ' END

分类:SQL服务器 时间:2015-03-16 人气:4
本文关键词: SQL服务器
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.596 (s). 10 q(s)