SQL拆分字符串构成新的记录

原数据表的数据格式如下

要求将字段vernacular中用逗号”,”分隔的字符串单独取出，并且把相应行的spnumber也取出，最后形成一条新的记录。

—拆分字段字符串形成新的记录

CREATE   TABLE tb

      spnumber INT,

      vernacular VARCHAR(1000)

INSERT  INTO tb

        SELECT  SPNUMBER,

                COMNAME

        FROM    dbo.CommonName

DECLARE @i INT

SELECT  @i = MAX(LEN(vernacular))

FROM    tb

SET ROWCOUNT @i

SELECT  spnumber = IDENTITY( INT)

INTO    #t

FROM    syscolumns a,

        syscolumns b

SET ROWCOUNT 0

SELECT  a.spnumber,

        vernacular = SUBSTRING(a.vernacular, b.spnumber,

                               CHARINDEX(‘,’, a.vernacular + ‘,’, b.spnumber)

                               – b.spnumber)

FROM    tb a,

        #t b

WHERE   SUBSTRING(‘,’ + a.vernacular, b.spnumber, 1) = ‘,’

ORDER   BY a.spnumber

DROP TABLE   #t

go

Comments