很多时候我们都会用到指定长度的流水号，假如生成订单编码规则我们设置为：仓库编码+年月日（6位）+当天四位流水号，如仓库编码为01日期为2009-08-06的第一个订单编码应该为：01200908060001，在这里指定长度的流水号的生成一般都是在数据库里操作，方法可以用下面几种：
方式一：笨办法，不够指定长度的话，前面循环加零
CREATE
FUNCTION
fn_GenerateSerialNumber
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
DECLARE @currentLen int
SET @currentLen = Len ( @result )
WHILE ( @currentLen < @length )
BEGIN
SET @result = ' 0 ' + @result
SET @currentLen = @currentLen + 1
END
RETURN @result
END
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
DECLARE @currentLen int
SET @currentLen = Len ( @result )
WHILE ( @currentLen < @length )
BEGIN
SET @result = ' 0 ' + @result
SET @currentLen = @currentLen + 1
END
RETURN @result
END
方式二：使用 Replace +Str 函数
CREATE
FUNCTION
fn_GenerateSerialNumber2
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
RETURN Replace ( Str ( @numberValue , @length ), ' ' , ' 0 ' )
END
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
RETURN Replace ( Str ( @numberValue , @length ), ' ' , ' 0 ' )
END
方式三： 使用 Replicate 函数
CREATE
FUNCTION
fn_GenerateSerialNumber3
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
SET @result = Replicate ( ' 0 ' , @length - Len ( @result )) + @result
RETURN @result
END
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
SET @result = Replicate ( ' 0 ' , @length - Len ( @result )) + @result
RETURN @result
END
方式四：使用 Right + Replicate 函数
CREATE
FUNCTION
fn_GenerateSerialNumber4
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
SET @result = Right ( Replicate ( ' 0 ' , @length ) + @result , @length )
RETURN @result
END
(
@numberValue int , -- 流水号数值
@length int -- 流水号字符串指定长度
)
RETURNS varchar ( 20 )
AS
BEGIN
DECLARE @result varchar ( 20 )
SET @result = Cast ( @numberValue as varchar )
SET @result = Right ( Replicate ( ' 0 ' , @length ) + @result , @length )
RETURN @result
END
另外，对于方式四，还可以考虑
SET
@result
=
Reverse
(
Substring
(
Reverse
(
@result
)
+
Replicate
(
'
0
'
,
@length
),
1
,
@length
))
等很多种办法，字符串函数组合有 N 多。
