--1. Old workaround (SQL Server)CREATE TABLE TB (ID int,value varchar ())INSERT into TB values (1, ' aa,bb ')INSERT into TB values (2, ' AAA,BBB,CCC ')Go--Method 1. Using temporary table completionSELECT TOP 8000 id = IDENTITY (int, 1, 1) to # from syscolumns A, syscolumns bSELECT a.id, value = SUBSTRING (A.[value], b.id, CHARINDEX (', ', a.[value] + ', ', b.id)-b.id)From TB A, # BWHERE SUBSTRING (', ' + a.[value], b.id, 1) = ', ' DROP TABLE #--Method 2. If the amount of data is small, temporary tables are not usedSelect a.id, value = substring (a.value, B.number, charindex (', ', A.value + ', ', B.number)-B.number)From TB a join master: Spt_values bOn b.type= ' P ' and b.number between 1 and Len (a.value)where substring (', ' + A.value, b.number, 1) = ', ' --2. New workaround (SQL Server 2005)CREATE TABLE TB (ID int,value varchar ())INSERT into TB values (1, ' aa,bb ')INSERT into TB values (2, ' AAA,BBB,CCC ')Go--Method 1. Using XML to completeSELECT a.id, B.value from( SELECT ID, [value] = CONVERT (XML, ' + REPLACE ([value], ', ', ') + ') from TB ) A OUTER APPLY( SELECT value = N.v.value ('. ', ' varchar ') from a.[value].nodes ('/root/v ') N (v) ) B--Method 2. Using a CTE to complete; with TT as(select Id,[value]=cast (Left ([Value],charindex (', ', ' [value]+ ', ')-1) as nvarchar (), split=cast (Stuff ([value] + ', ', 1,charindex (', ', [value]+ ', '), ') as nvarchar (+)) from TBUnion AllSelect Id,[value]=cast (Left (Split,charindex (', ', Split)-1) as nvarchar), split= cast (stuff (split,1, CHARINDEX (', ', Split), ') as nvarchar (+)) from TT where Split> ')Select Id,[value] from the TT ORDER by ID option (maxrecursion 0)DROP TABLE TB
Simple data splitting of SQL Server
