我是新手,需要帮助。
我有一张名为“员工”的表。它有 2 个字段 [number] 和 [encode]。我想自动获取输入到 [number] 中的任何数字并将其存储在 [encode] 中,以便在其前面添加适当数量的 0,以始终构成 12 位数字。
例子:
用户在[number]中输入“123”,“000000000123”将自动存储在[encode]中
用户在[number]中输入“123456789”,“000123456789”会自动存储在[encode]中
我想我想编写一个触发器来完成此操作。我认为这会在数据输入时发生。对吗?
主要思想是这样的:
variable1 = LENGTH [number]
variable2 = REPEAT (0,12-variable1)
variable3 = CONCATENATE (variable2, [number])
[encode] = variable3
我只是不知道足以实现这
一点任何帮助都会很棒。
我有 SQL-SERVER 2005 并且两个字段都是文本
I am a newbie and need help.
I have a table called "Employees". It has 2 fields [number] and [encode]. I want to automatically take whatever number is entered into [number] and store it in [encode] so that it is preceded by the appropriate amount of 0's to always make 12 digits.
Example:
user enters '123' into [number], '000000000123' is automatically stored in [encode]
user enters '123456789' into [number], '000123456789' is automatically stored in [encode]
I think i want to write a trigger to accomplish this. I think that would make it happen at the time of data entry. is that right?
The main idea is would be something like this:
variable1 = LENGTH [number]
variable2 = REPEAT (0,12-variable1)
variable3 = CONCATENATE (variable2, [number])
[encode] = variable3
I just don't know enough to make this happen
ANY help would be FANTASTIC.
I have SQL-SERVER 2005 and both fields are text
发布评论
评论(1)
以下链接提供了两种用 0 填充数字以使其达到特定长度的方法。
http://blog.sqlauthority.com/2009/03/10/sql-server-pad-ride-side-of-number-with-0-fixed-width-number-display/
在触发器中,它可能看起来像这样:
我当前没有 SQL Server,因此代码可能不完全正确,但应该引导您走向正确的方向。
The following link provides two methods to pad a number with 0's to make it a specific length.
http://blog.sqlauthority.com/2009/03/10/sql-server-pad-ride-side-of-number-with-0-fixed-width-number-display/
In a trigger it might look like this:
I don't have SQL Server currently, so the code might not be exactly correct, but is should steer you in the right direction.