在sql中输入格式数据，例如001,003,087,234 [英] Enter formated data in sql like 001,003,087,234

问题描述

我想以以下格式在sql中存储数据

0001
0002
0003
0004
.
.
.
.
9999

我的桌子是

创建表tblAssest(代码nvarchar(4))

等待一个肯定的答案........

I want to store data in sql as following format

0001
0002
0003
0004
.
.
.
.
9999

my table is

create table tblAssest (code nvarchar(4))

waiting for a positive answer........

推荐答案

这是一种方法

Here is one approach

--create table tblAssest (code nvarchar(4))

DECLARE @InitialCount INT
DECLARE @FinalCount INT

SET @InitialCount = 1
SET @FinalCount = 9999

WHILE  @InitialCount <= @FinalCount
BEGIN

    INSERT INTO tblAssest
    SELECT RIGHT('000' + CAST(@InitialCount AS VARCHAR),4)

    SET @InitialCount = @InitialCount + 1

END

SELECT * FROM tblAssest

--DROP TABLE tblAssest

有点不同的答案.如果您实际上是在存储数字，请不要在表中格式化它们，永远不要！而是将它们存储为数字，并在客户端处理所有格式要求.

该数据库用于存储数据.不是格式化的值.当数据以其自然形式存在并且将数据呈现给用户时，它会使用有关客户端的足够信息进行正确格式化，从而获得最佳结果.

A bit different answer. If you''re actually storing numbers, don''t format them in a table, never! Instead store them as numbers and handle all the formatting requirements on the client side.

The database is meant to store... well the data. Not the formatted values. The best results are achieved when the data is in it''s natural form and when the data is represented to the user it''s formatted properly using sufficient information about the client.

假设只需使用T-SQL，这里有一些不错的方法: http://sqlusa.com/bestpractices2005/padleadingzeros/ [< ^ ]，

Assuming that you have to use just T-SQL, here are some good approaches: http://sqlusa.com/bestpractices2005/padleadingzeros/[^], http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length[^]

这篇关于在sql中输入格式数据，例如001,003,087,234的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！