我们如何生成字母数字id [英] How we can generate alpha numeric id

问题描述

我们如何在sql server中生成字母数字CustomerID。例如，SC-12-10-26-0001，这里SC为每个客户固定，12显示当前年份，10显示当前月份，26显示当前日期，0001显示当前日期的第一个cutomer。我想根据这个生成ID。如果新的一年，一个月或一个日期即将到来，那么它就会发生变化。 Pl'z指导我如何解决这个问题.......

How we can generate a alpha-numeric CustomerID in sql server. For example, SC-12-10-26-0001, here SC is fix for every customer, 12 is showinng current year,10 is showing current month, 26 is showing current date and 0001 is showing first cutomer of the current date. I want to generate ID according to this. If new year, month or date is coming then it's change accroding to this. Pl'z guide me how we can solve this.......

推荐答案

这些问题。这是一个链接以及

something along these lines. here is a link aswell

CREATE
 TABLE
 YourTable (
  ID    AS  'SC'
 + convert(varchar, getdate(), 101)
 + RIGHT('0000' + convert(varchar,ID),6))

这里是一个链接以及



http://social.msdn.microsoft.com/Forums/en/transactsql/thread/1e3ada09-492b-46a2-9f65-c0f3990e2343 [ ^ ]

请参阅以下查询，它可能会对您有所帮助。

Hi,

See the below query it might be help you.

Declare @LastDigit NVARCHAR(5)
Select @LastDigit = ISNULL(Substring(CustomerID,13,len(CustomerID)),0) from dbo.Table_9
Set @LastDigit = MAX(Convert(Bigint,ISNULL(@LastDigit,0))) + 1
Set @LastDigit = RIGHT('000' + convert(varchar,@LastDigit),4)
Declare @CustomerID Nvarchar(50)
Set @CustomerID = 'SC-' +  substring(convert(varchar,Datepart(yy,GETDATE())),3,4)
+ '-' +  convert(varchar,Datepart(mm,GETDATE())) + '-' +
convert(varchar,Datepart(dd,GETDATE()))
+ '-' + @LastDigit
print(@CustomerID)

谢谢，

Viprat

Thanks,
Viprat

这篇关于我们如何生成字母数字id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！