concatenate a zero onto sql server select value shows 4 digits still and not 5

Question

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.

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

   (0 + [ZIP]) as 'fullzip'

ZIP is a float in db table

my full 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

Why on earth are zip codes being stored as floats? Using `LEN()` is meaningless then (without implicit conversion), because numbers don't have a "length". — Dai, Mar 08 '17 at 00:21
Oh, I was given an excel file and i did a import wizard and I noticed the float after the fact , thus it was then either build a table first and do a ssis import or just fix the data ... so i was just adding another column of int and going to update into it — , Mar 08 '17 at 00:23
No, don't use an `int` either, instead use a `varchar(10)` instead - because **zip codes are not numbers**, they're digit-strings (like phone numbers). It also allows you to store "ZIP+4" codes (9 digits with a dash) and Canadian postal codes too. — Dai, Mar 08 '17 at 00:46

score 2 · Accepted Answer · edited Jun 18 '19 at 18:00

2

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

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

Doesn't assume minimum values of 4 digits.

edited Jun 18 '19 at 18:00

Frost10000

21
8

answered Mar 08 '17 at 00:06

Rawheiser

1,200
8
17

If it already has a 5 digit zip code , then it will leave it alone, right? e.g. If it is 85226 , it is not going to do 085226 , right? thx – Mar 08 '17 at 00:20

score 0 · Answer 2 · answered Mar 08 '17 at 00:43

Another option where you can use the implicit conversion (no need to convert)

Declare @YourTable table (Zip float)
Insert Into @YourTable values
(12345),
(1234),
(123)

Select right('00000'+left(Zip,5),5)
 From  @YourTable

Returns

(No column name)
12345
01234
00123

score 0 · Answer 3 · edited May 23 '17 at 12:17

Instead of converting ZIP to varchar, I would use cast instead as it is ANSI standard.

SELECT
    TOP 1000
   [ZIP]
  ,ZipCode = CASE WHEN LEN(oa_zip.fullzip) = 4 then '0' + oa_zip.fullzip ELSE oa_zip.fullzip END
  ,[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
OUTER APPLY (SELECT fullzip = CAST(ZIP AS VARCHAR)) oa_zip

score -1 · Answer 4 · answered Mar 08 '17 at 00:00

-1

You're using 0 which is an integer literal, not a string value. I suspect that implicit-conversion is converting your zip to an integer and then adding 0 (which is a non-operation), instead of doing string concatenation. To be safe you should use a string literal '0' instead of an integer literal 0, and use the CONCAT function to force concatenation instead of implicitly allowing addition instead:

SELECT
    CONCAT( '0', [Zip] ) AS [FullZip],
    -- etc

answered Mar 08 '17 at 00:00

Dai

141,631
28
261
374

`'CONCAT' is not a recognized built-in function name.` well - maybe the sql server is too old ? – Mar 08 '17 at 00:17
@JeremyMiller What compatibility level are you in? `CONCAT` was added in SQL Server 2012 (compatibility level `110`). Alternatively use `+` but explicitly cast all operands to `varchar`/`nvarchar`. – Dai Mar 08 '17 at 00:20
Where do i find out ? I can google... I started at a new company and i think that they have a bunch of old sql server databases – Mar 08 '17 at 00:21
@JeremyMiller SQL Server Management Studio will tell you what the Compatibility Level of a database is in the Database Properties page. Note that a database's compatibility level is different compared to the sever's version (i.e. compatibility-level is a per-database option, not a per-server option). – Dai Mar 08 '17 at 00:48

concatenate a zero onto sql server select value shows 4 digits still and not 5

4 Answers4

Linked