生成此范围内的收据编号 [英] Generate a receipt number in this range
本文介绍了生成此范围内的收据编号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在以下范围内生成唯一的收据编号: GA00000-GZ99999?我不允许使用'I'和'O'字母,因此GI00000-GI99999& GO00000-GO99999将被排除在外.
How would I go about generating a unique receipt number in the following range: GA00000-GZ99999? I am not allowed to use the 'I' and 'O' letters so GI00000-GI99999 & GO00000-GO99999 would be excluded.
理想情况下,我想在T-SQL中创建它,但也可以在VB.Net中创建它.该编号将存储在SQL中,在生成下一个编号之前,我可以对其进行访问.它们不必是顺序的.
Ideally, I'd like to create this in T-SQL but can also do it in VB.Net. This number will be stored in SQL and I can access it prior to generating the next one. They do not have to be sequential.
谢谢.
推荐答案
我决定按照以下步骤进行操作:
I decided to do it as the following:
CREATE FUNCTION [dbo].[fn_generateReceiptNumber]()
RETURNS NCHAR(7)
AS
BEGIN
-- Declare the return variable here
DECLARE @result NCHAR(7);
DECLARE @prefix NCHAR(1);
DECLARE @suffix INT;
DECLARE @currentMax NCHAR(7);
SELECT @currentMax = MAX(ISNULL(fp.CustomReceiptNo, 'GA00001')) FROM dbo.FinPayment fp;
SELECT @prefix = SUBSTRING(@currentMax,2,1);
SELECT @suffix = CAST(SUBSTRING(@currentMax,3,7) AS INT);
IF((@suffix + 1) > 99999)
BEGIN
SELECT @suffix = 0;
END
ELSE
BEGIN
SELECT @suffix = @suffix + 1;
END
IF(@suffix = 0)
BEGIN
IF(@prefix = 'Z')
BEGIN
RETURN -1;
END
ELSE
BEGIN
IF(NCHAR(UNICODE(@prefix)+1) IN ('I', 'O'))
BEGIN
SELECT @prefix = NCHAR(UNICODE(@prefix)+2);
END
ELSE
BEGIN
SELECT @prefix = NCHAR(UNICODE(@prefix)+1);
END
END
END
-- Return the result of the function
SELECT @result = NCHAR(71) + @prefix + CAST(RIGHT('00000' + RTRIM(@suffix), 5) AS NCHAR(5));
RETURN @result;
END
GO
感谢大家的投入. 史蒂夫.
Thank you everyone for the input. Steve.
这篇关于生成此范围内的收据编号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文