CODEWITHSUNDEEP
sql-server
Marin
Marin

Reputation: 12910

Not able to remove first comma in string SQL Server

The code works fine except for 

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

which is giving me problems. I do not know how to get rid of the first comma. I have tried substring but it removes all the commas(I guess since it's in a select statement). I even tried COALESCE(@FEATURE + ', ', '') + FEATURE but I end up with the same result, either no commas or one extra comma at the end or at the very beginning. 

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);

Upvotes: 0

Views: 3093

Answers (3)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Use Stuff but on the outer side of subquery:

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, '')

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Use the STUFF function to remove the leading comma.

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,''),

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112372

I am not sure what you are trying to achieve. If my guess was wrong, it would good if you told us what results you expect in which situation.

Try this

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

Upvotes: 0

Related Questions