用零填充varchar(5)字段中的第一个未用数

Question

我有一个varchar(5)字段。我需要在所有记录中找到第一个未使用的数字，以便它们以前导零开始。比如，如果有00001和00003，我希望查询返回00002。此外，许多记录包含字母，看上去像‘G 0542’。这些是可以忽略的。

我知道我很接近。这似乎适用于Server 2005，但不适用于2008年或2012年。

http://sqlfiddle.com/#!3/4016a0/1

create table b_addr ( inst_no varchar(5) Unique );
insert into b_addr (inst_no) values ('00001');
insert into b_addr (inst_no) values ('00002');
insert into b_addr (inst_no) values ('00004');
--this is the problem line 
insert into b_addr (inst_no) values ('A0045');

With usedNos as( 
select  CAST(b_addr.inst_no AS INT) as inst 
from b_addr 
where b_addr.inst_no LIKE '[0-9][0-9][0-9][0-9][0-9]') 
SELECT 
RIGHT('00000' + CONVERT(VARCHAR(5), COALESCE(min(inst)+1, 0)),5) AS next_inst_no 
from usedNos where not exists (select null from usedNos usn where usn.inst = usedNos.inst +1) 

我如何构造它，以便它也能在sql server 2008+中工作呢？

Answer 1

下面是一个有效的查询版本：

With usedNos as ( 
      select CAST(case when isnumeric(b_addr.inst_no) = 1
                       then b_addr.inst_no 
                  end AS INT) as inst 
      from b_addr 
     ) 
SELECT RIGHT('00000' + CONVERT(VARCHAR(5), COALESCE(min(inst)+1, 0)),5) AS next_inst_no 
from usedNos
where inst is not null and 
      not exists (select 1
                  from usedNos usn
                  where usn.inst = usedNos.inst +1
                 );

关键是isnumeric()在case中的使用。这保证了除非该值看起来是数字，否则不会尝试cast()。如果它看起来不像一个数字，那么结果是NULL，它在外部where子句中被过滤掉。

您的where条款：

      where b_addr.inst_no LIKE '[0-9][0-9][0-9][0-9][0-9]'

试图做同样的事。但是，Server并不保证在where之前处理select子句--这就是为什么您会收到意外的错误。

Answer 2

您可以检查您要转换的数字是否为实数：

而不是

CAST(b_addr.inst_no AS INT) as inst

做一些类似的事情

CAST(CASE WHEN ISNUMERIC(b_addr.inst_no) = 1 THEN b_addr.inst_no ELSE 0 END AS INT) as inst

有点复杂，但很管用。

演示： http://sqlfiddle.com/#!3/1aee5/6

Answer 3

create table b_addr ( inst_no varchar(5) Unique );
insert into b_addr (inst_no) values ('00001');
insert into b_addr (inst_no) values ('00002');
insert into b_addr (inst_no) values ('00004');
--this is the problem line 
insert into b_addr (inst_no) values ('A0045');

--Specify how many of the "next" unused numbers you want.
DECLARE @HowMany INT = 10

--Common Table Expression construct to generate sequential numbers.
;WITH 
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  L6   AS(SELECT 1 AS c FROM L5 AS A, L5 AS B),
  SequentialNumbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Num FROM L6)
SELECT TOP(@HowMany) RIGHT('00000' + CAST(sn.Num AS VARCHAR), 5) AS NextNumbers
FROM SequentialNumbers sn
WHERE sn.Num NOT IN ( 
    SELECT  CAST(b_addr.inst_no AS INT) AS inst 
    FROM b_addr 
    WHERE b_addr.inst_no LIKE '[0-9][0-9][0-9][0-9][0-9]'
)