为什么(以及如何)使用 master..spt_values 拆分列?

What is the (meaning of) Type = 'P' and why to use undocumented master..spt_values for splitting a column? What is the benefit of it?

[ 1 ]

CREATE TABLE dbo.Table1 
(
    Col1        CHAR(1),
    Col2        CHAR(1),
    Col3        CHAR(1),
    Col4        VARCHAR(50)
)
GO

INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
GO
INSERT INTO dbo.Table1 VALUES ('D','E','F','6,7,8,9')
GO


SELECT
    T.col1, RIGHT(LEFT(T.col4,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
    master..spt_values,
    table1 T
WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
    (SUBSTRING(T.col4,Number,1) = ','
    -- OR SUBSTRING(T.col4,Number,1)  = '') --this does not work correctly anyway

相关问题:

• system table table master..spt_values 的用途是什么，其值的含义是什么?

推荐答案

目的

为什么使用未记录的master..spt-values

Sybase 及其私生子 MS SQL 为产品提供了各种特性和功能，这些特性和功能在系统过程中实现(与作为服务启动的 sqlserver 等二进制文件相反).这些系统过程程序是用 SQL 代码编写的，并命名为 sp_%. 除了一些秘密的内部结构外，它们与任何其他 SQL 代码具有相同的限制和需求.它们是 Sybase ASE 或 SQL Server 产品的一部分.因此，他们不需要记录它；并且内部位不能被合理地标记为未记录".

    SELECT *                    -- list Genders
        FROM Gender 

    SELECT  P.*,                -- list Person
            G.Name              -- expand GenderCode to Name
        FROM Person P
        JOIN Gender G
            ON P.GenderCode = G.GenderCode

    SELECT *                    -- list LockTypes
        FROM master..spt_values 
        WHERE type = "L"

    SELECT  spid,               -- list Active Locks
            DB_NAME(dbid),
            OBJECT_NAME(id, dbid),
            v.name,             -- expand lock name
            page,
            row
    FROM master..syslocks   L,
         master..spt_values LT
    WHERE L.type = LT.number    -- 
    AND   type = "L"            -- LockType Lookup table
    ORDER by 1, 2, 3, 4, 5, 6   -- such that perusal is easy

    SELECT  LT.name,            -- list LockTypes
            [Count] = (         -- with count
        SELECT COUNT(*)
            FROM master..syslocks
            WHERE type = LT.number
                )
        FROM master..spt_values LT
        WHERE type = "L"