How do I replace the final comma with the word [and] in a comma-separated string?

Question

question

b.joseph asked May 9, '16

How do I replace the final comma with the word [and] in a comma-separated string?

Hi, Please help me. How can I add commas and "and" before the end item in a comma-separated list. Can I use REPLACE in a loop? I have a scenario. I have courses and pre-requisite courses in my table. You can find the SQL script to create and insert the table below:

COURSE_ID   PRE_COURSE_ID    OPERATOR
--------------------------------------------
IT105       IT100            AND
IT105       IT101              
IT106       1T103
BM110       BM101            AND
BM110       BM102            AND
BM110       BM103
CC107       CC103            OR
CC107       CC104
-------------------------------------------------------

I am expecting the output to look like below:

COURSE_ID PRE_COURSE_ID
------------------------
IT105     IT100 and IT101
IT106     IT103
BM110     BM101,BM102 and BM103
CC107     CC103 OR CC104

Here is my SQL script for this table CREATE TABLE test_Course ( course_ID VARCHAR(10) , pre_course_id VARCHAR(10) , operator VARCHAR(5) ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'IT105' , 'IT100' , 'and' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'IT105' , 'IT101' , '' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'IT106' , 'IT103' , '' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'BM110' , 'BM101' , 'and' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'BM110' , 'BM102' , 'and' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'BM110' , 'BM103' , '' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'CC107' , 'CC103' , 'or' ); INSERT INTO test_Course ( course_ID , pre_course_id , operator ) VALUES ( 'CC107' , 'CC104' , '' );

sql server replace comma separated

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO commented · May 10, 2016 at 08:00 PM

Sorry, I don't have time to answer this right now (I may get back to it later today) but to get you started, think about first using FOR XML PATH to create your comma separated list, then use REVERSE and CHARINDEX and REPLACE to find the last comma and replace it with an (reversed) AND or an OR. Finally REVERSE again to return the string to its original order.

2 ·

b.joseph commented · May 11, 2016 at 12:19 PM

Thanks @GPO. So as per your solution, I can arrange the column with comma separated items, then reverse the entire field ,replace the first comma with 'dna' (the reversed form of 'and') then reverse it again?

0 ·

GPO commented · May 11, 2016 at 12:44 PM

It wasn't exactly as I had envisaged (heh... it never is!) but it wasn't too far off. Paste the code below into SSMS and see how you go. If it meets your needs don't forget to mark it as the correct answer.

0 ·

Answer 1 · 2016-05-11T12:41:59Z

GPO answered May 11, '16

CREATE TABLE #test_Course ( course_ID VARCHAR(10), pre_course_id VARCHAR(10), operator VARCHAR(5), seq smallint not null --You'll need a sequence number if you want to guarantee the order of the items in the string. ); INSERT #test_Course(course_ID,pre_course_id,operator,seq) --Since SQL Server 2008 we've been able to INSERT using this syntax shorthand VALUES ('IT105','IT100','and',1), ('IT105','IT101','' ,2), ('IT106','IT103','' ,1), ('BM110','BM101','' ,1), --You had an AND here. I don't think you meant that. It doesn't fit the pattern. ('BM110','BM102','and',2), ('BM110','BM103','' ,3), ('CC107','CC103','or' ,1), ('CC107','CC104','' ,2); --======= NOTE: when using in a stored proc you may need this setting at the top of your sproc SET ARITHABORT ON; --------- ------------------------------------------------------------------------------------------------- WITH group_concat as ( SELECT norm1.course_id ,STUFF( --This implementation of STUFF replaces the two characters starting at position 1 with an empty string (trims off the leading ", ") ( SELECT ', ' + norm2.pre_course_id + iif(norm2.operator '',' ' + norm2.operator,'') FROM #test_Course norm2 WHERE norm2.course_id = norm1.course_id ORDER BY norm2.seq --Need this to determine the order of the concatenated string FOR XML PATH(''),type ).value('.', 'nvarchar(max)'), 1, 2, '' -- the [.value('.', 'nvarchar(max)')] bit unescapes the XML escape characters. ) as pre_course_ids FROM #test_Course norm1 --from the normalised data GROUP BY norm1.course_id ) , first_adjustments as --you could do the following gymnastics all as one SELECT. I've just split it up to make the code easier to read. ( SELECT course_id ,REVERSE(pre_course_ids) as reversed_string ,charindex(',',REVERSE(pre_course_ids),1) as first_comma_position FROM group_concat ) SELECT course_id ,reverse(iif(first_comma_position >0,stuff(reversed_string,first_comma_position,1,''),reversed_string)) as pre_course_ids FROM first_adjustments ;

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

b.joseph commented · May 12, 2016 at 05:18 AM

Hi GPO, Yes it is working fine,thanks! Let me apply on this my table.

1 ·

question