将零连接到 sql server 选择值仍然显示 4 位而不是 5

问题描述

我的邮政编码在导入时从 excel 文件中没有为零.所以我做了一个选择，以便将 0 连接到每 4 位邮政编码的前面.

I have zip codes that on import didn't have zero from excel file. So I was doing a select in order to concatenate 0 to front of every 4 digit zip code.

我正在尝试这个，但它仍然吐出 4 个数字

I was trying this, but it still spits out 4 digits

   (0 + [ZIP]) as 'fullzip'

ZIP 是 db 表中的浮点数

ZIP is a float in db table

我的完整sql

SELECT
    TOP 1000
   [ZIP]
  ,(0 + [ZIP]) as 'fullzip'
  ,[ZIP_Name]
  ,[ZIP_CountyFIPS]
  ,[ZIP_County]
  ,[ZIP_State]
  ,[Utility_Name]
  ,[Holding_Company]
  ,[Utility_ID]
  ,[GAS_LDC_Type]
  ,[ELEC_Non_IOU_Type]
  ,[Percent_of_Overlap]
  ,[Utility_Territory_Type]
FROM
    cc.dbo.ServiceableZipCodes
WHERE
    Len( [ZIP] ) = 4

推荐答案

如果 zip 是浮点数，我会转换为 char，然后做字符串数学.

If zip is a float, I'd convert to char, then do the string math.

RIGHT( '00000' + CONVERT(varchar(5),ZIP), 5)

不假设最小值4 位数字.

Doesn't assume minimum values of 4 digits.