Zip codes are strings, not numbers. Some of them have 1 or even 2 (but not more than 2) leading zeroes. The datatype in the import table should be VARCHAR(10)
so that it can hold 5 digit and 9 digit + hyphen zip codes. Even if you never have to store postal codes of other countries, and even if the values will only ever have numeric digits (i.e. 0 - 9), this data is still string data, just like phone numbers.
Based on the import query shown in your other question (automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS ):
SELECT * INTO XLS_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
'SELECT * FROM [OH$]');
I would suggest not relying upon the SELECT INTO
construct to create the XLS_IMPORT
table, but instead create the import table(s) manually, and then use the INSERT INTO ... SELECT FROM OPENROWSET()
construct. Doing this would allow you to do the following things to improve this situation:
- Create the
ZipCode
field as VARCHAR(10)
- Convert the values on the way in using the STR function which, for an initial
FLOAT
value of 432013256
, will return 432013256
instead of 4.32013e+008
(which is what you get when you convert to VARCHAR
).
Fix any missing leading zeroes using something like the following:
CASE
WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
ELSE 'BadZipCode'
END
Example:
DECLARE @ZipColumn FLOAT = 032013256.000000;
SELECT CASE
WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
ELSE 'BadZipCode'
END;
Returns:
032013256
Ideally, you would fix the column definition in the spreadsheet to be a string. But even if you do that, it might still be a good idea to keep this code around.
My end goal is to simply get the 9 digit zip code to have a hyphen in the middle.
With that goal in mind, the following inline TVF can be used to both convert the FLOAT
value to VARCHAR
, and to add in the hyphen for ZIP + 4 values.
Code for iTVF:
CREATE FUNCTION dbo.FormatZIPCode(@NumericZIPCode FLOAT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH string AS
(
SELECT LTRIM(STR(@NumericZIPCode)) AS [Value],
LEN(LTRIM(STR(@NumericZIPCode))) AS [Size]
), converted AS
(
SELECT CASE
WHEN st.[Value] IS NULL THEN NULL
WHEN st.[Size] BETWEEN 3 AND 4
THEN RIGHT('0000' + st.[Value], 5)
WHEN st.[Size] BETWEEN 7 AND 8
THEN RIGHT('0000' + st.[Value], 9)
WHEN st.[Size] IN (5, 9)
THEN st.[Value]
ELSE 'BadZipCode'
END AS [ZIP],
st.[Size] AS [OriginalSize]
FROM string st
)
SELECT IIF(cnv.[OriginalSize] >= 7, STUFF(cnv.[ZIP], 6, 0, '-'), cnv.[ZIP])
AS [FormattedZIPCode]
FROM converted cnv;
Test:
SELECT *
FROM (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
(123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(src.[val]) frmt;
Returns:
val FormattedZIPCode
--------- ----------------
NULL NULL
1 BadZipCode
12 BadZipCode
123 00123
1234 01234
12345 12345
123456 BadZipCode
1234567 00123-4567
12345678 01234-5678
123456789 12345-6789
In order to be clearer about what is being suggested, the following shows all of the suggestions noted above put together:
CREATE TABLE dbo.XLS_IMPORT
(
Col1 DataTypeForCol1,
Col2 DataTypeForCol2,
ZIPCode VARCHAR(10),
...
);
INSERT INTO dbo.XLS_IMPORT (Col1, Col2, ZIPCode, ...)
SELECT xls.Col1, xls.Col2, zip.[FormattedZIPCode], ...
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
'SELECT * FROM [OH$]')
CROSS APPLY dbo.FormatZIPCode(xls.[ZIP]) zip;
Some of the spreadsheets are "aware" of there being leading zeroes, and so prefixed the field in Excel with a single apostrophe so that Excel would treat the value as a string instead of as numeric (e.g. '01234
). In that case, you can use the REPLACE
function to strip out that apostrophe.
-- Test incoming string data (potentially prefixed with a single apostrophe)
SELECT src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM (VALUES (NULL), (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(REPLACE(src.[val], N'''', N'')) frmt;
However, you can't use REPLACE
on all spreadsheets because the spreadsheets that have the ZIP
column as numeric will do a CONVERT_IMPLICIT
as it passes the value to the REPLACE
function and the converted value will be in scientific notation (e.g. 1.23457e+008
). So if you can't be certain which datatype is going to be returned by OPENROWSET
, then you can use IIF
(or CASE
if using a version of SQL Server prior to 2012) and CHARINDEX
to test for the presence of an apostrophe. If the data is being returned as FLOAT
, then the implicit conversion to VARCHAR
that will occur when passing the values into CHARINDEX
will not be an issue since there is no apostrophe in 1.23457e+008
and the converted value is only going to CHARINDEX
and not to dbo.FormatZIPCode
.
-- Test handling incoming data as both numeric and string
SELECT src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
(123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(
IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
) frmt;
SELECT src.[val], frmt.[FormattedZIPCode]
FROM (VALUES (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(
IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
) frmt;