方式一:笨办法,不够指定长度的话,前面循环加零
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多。