将两列之间的分隔范围分隔为行

Question

我有一个表如下

CREATE TABLE [dbo].[CODE](
    [BEG] [varchar](10) NOT NULL,
    [END] [varchar](10) NOT NULL,
    [EFF_BEG] [smalldatetime] NOT NULL
) ON [PRIMARY]
Insert into CODE select 'H0411', 'H0413', '01/01/2015'
Insert into CODE select 'J090', 'J093', '01/01/2015'

我想获得BEG和END中所有代码的列表，如下所示

Instance
-------
H0411
H0412
H0413
J090
J091
J092
J093

我有类似下面的东西，但它不能工作在varchar列

WITH number_cte (n)
AS (
    SELECT n
    FROM (
        SELECT 0 n
        ) m
    UNION ALL
    SELECT n + 1 n
    FROM number_cte
    WHERE n < 2400
    )
SELECT BEG+n instance
    ,EFF_BEG
FROM CODE
JOIN number_cte ON BEG + n BETWEEN BEG
        AND END
WHERE BEG + n - 100 * floor((BEG + n) / 100) BETWEEN 0
        AND 59
ORDER BY 1
OPTION (MAXRECURSION 2401)

有解决办法吗?由于

Answer 1

实现这一点的一种方法是通过Numbers表或CTE，您可以像这样连接到CODE表:

WITH NUMBERS AS ( -- change limit if needed
  SELECT TOP 10000 row_number() over(order by t1.number) as Number
  FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2),
CODE_SPLIT AS
( SELECT 
    BEG, [END],
    SUBSTRING(BEG, 1, PATINDEX('%[1-9]%', BEG)-1) as FirstChars,
    CONVERT(int, SUBSTRING(BEG, 2, LEN(BEG) - 1)) as BegInt,
    CONVERT(int, SUBSTRING([END], 2, LEN([END]) - 1)) as EndInt
  FROM [dbo].[CODE]
)
SELECT c.*,
 c.FirstChars + CAST(n.Number as VARCHAR) as Result
FROM 
 CODE_SPLIT c INNER JOIN 
 NUMBERS n ON n.Number BETWEEN BegInt AND EndInt;

我在输出中留下所有字段进行验证，结果在Result列中。

所做的主要假设是代码由两个顺序对应的部分组成:

• 单个非数字字符;
• 可以有前导零的数字。

要创建永久的Numbers表，可以使用以下代码:

CREATE TABLE NumbersTest (Number  int  not null)  
INSERT INTO NumbersTest(Number)
SELECT TOP 10000 row_number() over(order by t1.number) as N
FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2;