DECLARE @TempTable TABLE
(
ID VARCHAR(16)
)
DECLARE @Temp VARCHAR(16)
DECLARE @Num VARCHAR(11)
DECLARE @SortIndex INT
SET @SortIndex=0
SET @Num='00000100000'
while(@SortIndex<1000)
BEGIN
SET @Num=@Num+1
IF(len(@Num)=6)
BEGIN
SET @Num='00000'+@Num
END
ELSE IF(len(@Num)=7)
BEGIN
SET @Num='0000'+@Num
END
ELSE IF(len(@Num)=8)
BEGIN
SET @Num='000'+@Num
END
ELSE IF(len(@Num)=9)
BEGIN
SET @Num='00'+@Num
END
ELSE IF(len(@Num)=10)
BEGIN
SET @Num='0'+@Num
END
SET @Temp='abcdefghijklmnop'
SET @Temp=REPLACE(@Temp,'abc','123')
SET @Temp=REPLACE(@Temp,'d','4')
SET @Temp=REPLACE(@Temp,'efghijklmno',@Num)
SET @Temp=REPLACE(@Temp,'p','5')
INSERT INTO @TempTable(ID)VALUES(@Temp)
SET @SortIndex=@SortIndex+1
ENDSELECT ID FROM @TempTable
------------
1234000001000015
1234000001000025
1234000001000035
.....
1234000001010005其中我的@Num这个变量是从00000100000开始至99999999999结束,然后要让它自增。SET @Num=@Num+1,这个得出来的是1000001,我为了要补足前面几个零,用得笨办法。通过len()等于多少来补足零的个数,各位大虾,有没有好的方法可以改进我的代码
(
ID VARCHAR(16)
)
DECLARE @Temp VARCHAR(16)
DECLARE @Num VARCHAR(11)
DECLARE @SortIndex INT
SET @SortIndex=0
SET @Num='00000100000'
while(@SortIndex<1000)
BEGIN
SET @Num=@Num+1
IF(len(@Num)=6)
BEGIN
SET @Num='00000'+@Num
END
ELSE IF(len(@Num)=7)
BEGIN
SET @Num='0000'+@Num
END
ELSE IF(len(@Num)=8)
BEGIN
SET @Num='000'+@Num
END
ELSE IF(len(@Num)=9)
BEGIN
SET @Num='00'+@Num
END
ELSE IF(len(@Num)=10)
BEGIN
SET @Num='0'+@Num
END
SET @Temp='abcdefghijklmnop'
SET @Temp=REPLACE(@Temp,'abc','123')
SET @Temp=REPLACE(@Temp,'d','4')
SET @Temp=REPLACE(@Temp,'efghijklmno',@Num)
SET @Temp=REPLACE(@Temp,'p','5')
INSERT INTO @TempTable(ID)VALUES(@Temp)
SET @SortIndex=@SortIndex+1
ENDSELECT ID FROM @TempTable
------------
1234000001000015
1234000001000025
1234000001000035
.....
1234000001010005其中我的@Num这个变量是从00000100000开始至99999999999结束,然后要让它自增。SET @Num=@Num+1,这个得出来的是1000001,我为了要补足前面几个零,用得笨办法。通过len()等于多少来补足零的个数,各位大虾,有没有好的方法可以改进我的代码
select right('00000000000'+@num,11)
IF(len(@Num)=6)
BEGIN
SET @Num='00000'+@Num
END
ELSE IF(len(@Num)=7)
BEGIN
SET @Num='0000'+@Num
END
ELSE IF(len(@Num)=8)
BEGIN
SET @Num='000'+@Num
END
ELSE IF(len(@Num)=9)
BEGIN
SET @Num='00'+@Num
END
ELSE IF(len(@Num)=10)
BEGIN
SET @Num='0'+@Num
END 改为:
set @num=right('000000000'+@num,10)
BEGIN
SET @Num=@Num+1
SET @Num=replicate('0',11-len(@Num))+@Num
.
.
.
DECLARE @TempTable TABLE
(
ID VARCHAR(16)
)
DECLARE @Temp VARCHAR(16)
DECLARE @Num int
DECLARE @SortIndex INT
SET @SortIndex=0
SET @Num=100000
while(@SortIndex<1000)
begin
set @Num = @Num + @SortIndex
SET @Temp='abcdefghijklmnop'
SET @Temp=REPLACE(@Temp,'abc','123')
SET @Temp=REPLACE(@Temp,'d','4')
SET @Temp=REPLACE(@Temp,'efghijklmno',right('00000000000'+ltrim(@Num),11))
SET @Temp=REPLACE(@Temp,'p','5')
INSERT INTO @TempTable(ID)VALUES(@Temp)
SET @SortIndex=@SortIndex+1
ENDSELECT ID FROM @TempTable
SET @Temp=REPLACE(@Temp,'abc','123')
SET @Temp=REPLACE(@Temp,'d','4')
SET @Temp=REPLACE(@Temp,'efghijklmno',@Num)
SET @Temp=REPLACE(@Temp,'p','5')
declare @t table (col int)
insert into @t
select 123 union all
select 1 union all
select 45 union all
select 1234 union all
select 12456select right('0000000000'+LTRIM(col),10) from @t
/*
0000000123
0000000001
0000000045
0000001234
0000012456
*/