SQL handles data with commas

The purpose of this blog is to deal with the use of SQL to convert data with commas into rows in a table and to stitch rows from a table into data with commas.

The specific code below is written in the comments.

--convert data with commas to a table storage ID plus data godeclare @Text VARCHAR SET @Text = ' test1,test2 ' DECLARE @xml xmlset @xml = CONVERT (XML, ' <a > ' + REPLACE (@Text, ', ', ' </a><a> ') + ' </a> ') CREATE TABLE #TMP1 (iID varchar, stext varchar (200)) INSERT into #TMP1SELECT NEWID (), B.valuefrom (select @xml as ' xml ') ACROSS APPLY (select Tb.a.value ('. ', ' VARCHAR ") as Value from  a.xml.nodes ('/A ') as Tb (a)) bselect * from #TMP1GO--the data of a column in a TABLE is spelled in a comma-delimited form the go    CREATE TABLE #TMP2 (  IID Int,stext VARCHAR) insert into #TMP2 (IID, Stext) VALUES (1,  ' test1 ') and insert into #TMP2 (IID, stext) VALUES ( 1,  ' test2 ');D eclare @TMP2RES VARCHAR () Set @TMP2RES = (SELECT stext+ ', ' from #TMP2 for XML PATH (")) Set @TMP2RES = SUBSTRING (@TMP2RES, 0,len (@TMP2RES)-1) SELECT @TMP2RESGO

Run results

SQL handles data with commas