如何在sql中插入特定的格式编号 [英] How Can insert specific formate number in sql

问题描述

我需要插入数据库编号，格式如下：





0001

0002

0003

0005

i need insert into dataase number with format like this:


0001
0002
0003
0005

推荐答案

您可以将列定义为主键，然后使用您自己的逻辑将数据插入此键。

只要数据唯一，服务器就不会抱怨！



生成这种字符串很容易。使用 left（'XXXXXXXXXXX'+ rtrim（@str），@ n）之类的格式化数据。

You can define a column to be the primary key and then use your own logic to insert data into this key.
So long as the data unique, the server will not complain!

Generating this kind of string is easy. Use something like left('XXXXXXXXXXXX'+ rtrim(@str), @n) to format data.

你可以这样做：

You can do this:

SELECT Right('0000' + CONVERT(NVARCHAR, 1), 4) AS NewFormat
SELECT Right('0000' + CONVERT(NVARCHAR, 2), 4) AS NewFormat
SELECT Right('0000' + CONVERT(NVARCHAR, 3), 4) AS NewFormat
SELECT Right('0000' + CONVERT(NVARCHAR, 4), 4) AS NewFormat
SELECT Right('0000' + CONVERT(NVARCHAR, 12), 4) AS NewFormat

请使用

Please use as

create table tblEmployee
(
ID varchar(4),
Name varchar(50)
)

Create proc SP_InsertEmployee
@ID int,
@Name varchar(50)
As
Begin
insert into tblEmployee(ID,Name) values (RIGHT('0000' + CAST(@ID as varchar), 4),@Name)
End


SP_InsertEmployee 5,'PANKAJ'

select * from tblEmployee

这篇关于如何在sql中插入特定的格式编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！