导入前导零的数据-SQL Server [英] Import data with leading zeros - SQL Server

问题描述

我正在尝试将数据导入表中.我正在批量插入.我使用所有字段均为nvarchar(max)的CREATE语句创建了表.我不明白为什么导入完成后，带有前导零的数据已更改为科学计数法.为什么它不保留为文本并保留前导零?

I'm trying to import data into a table. I'm doing a bulk insert. I've created the table using a CREATE statement where all fields are nvarchar(max). I cannot understand why when the import is done, the data with leading zeros has been changed to scientific notation. Why does it not stay as text and preserve the leading zeros?

推荐答案

我建议您定义所需的零个数，然后进行更新.这是一个有10个零的示例.

I suggest that you define the number of zeros that do you want and then make an update. Here is an example with 10 zeros.

create table #leadingZeros(uglynumber nvarchar(max),handsomenumber nvarchar(max),nicenumber nvarchar(max))
INSERT INTO #leadingZeros VALUES(1000000,0000123,0500000)

SELECT * FROM #leadingZeros
--OUTPUT:
--uglynumber    handsomenumber  nicenumber
--1000000   123     500000
UPDATE #leadingZeros SET 
uglynumber=RIGHT('0000000000'+uglynumber,10)
,handsomenumber=RIGHT('0000000000'+handsomenumber,10)
,nicenumber=RIGHT('0000000000'+nicenumber,10)

SELECT * FROM #leadingZeros
--OUTPUT
--uglynumber    handsomenumber  nicenumber
--0001000000    0000000123  0000500000

这篇关于导入前导零的数据-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！