9 位邮政编码报告 LEN 为 12

邮政编码是字符串，而不是数字。其中一些有 1 个甚至 2 个（但不超过 2 个）前导零。导入表中的数据类型应VARCHAR(10)使其可以容纳 5 位和 9 位 + 连字符邮政编码。即使您永远不必存储其他国家/地区的邮政编码，即使这些值只有数字（即 0 - 9），该数据仍然是字符串数据，就像电话号码一样。

根据您的其他问题中显示的导入查询（自动导入和导出进程 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$]');

我建议不要依赖SELECT INTO构造来创建XLS_IMPORT表，而是手动创建导入表，然后使用INSERT INTO ... SELECT FROM OPENROWSET()构造。这样做可以让您做以下事情来改善这种情况：

1. 创建ZipCode字段为VARCHAR(10)
2. 在使用STR函数的过程中转换值，对于 的初始FLOAT值432013256，将返回 432013256而不是4.32013e+008（这是转换为 时得到的VARCHAR）。

3. 使用以下内容修复任何丢失的前导零：

   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
   

例子：

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;

回报：

032013256

理想情况下，您会将电子表格中的列定义固定为字符串。但即使您这样做，保留此代码可能仍然是一个好主意。

我的最终目标是简单地让 9 位邮政编码在中间有一个连字符。

考虑到这一目标，以下内联 TVF 可用于将FLOAT值转换为VARCHAR，并为 ZIP + 4 值添加连字符。

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;

测试：

SELECT  *
FROM    (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
                (123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(src.[val]) frmt;

回报：

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

为了更清楚地了解建议的内容，以下显示了上面提到的所有建议：

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;

一些电子表格“知道”有前导零，因此在 Excel 中的字段前加上一个撇号，以便 Excel 将值视为字符串而不是数字（例如'01234）。在这种情况下，您可以使用该REPLACE函数删除该撇号。

-- 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;

但是，您不能REPLACE在所有电子表格上使用，因为将ZIP列作为数字的电子表格在CONVERT_IMPLICIT将值传递给REPLACE函数时会执行 a 并且转换后的值将采用科学计数法（例如1.23457e+008）。因此，如果您不能确定 将返回哪种数据类型OPENROWSET，那么您可以使用IIF（或者CASE如果使用 2012 年之前的 SQL Server 版本）并CHARINDEX测试是否存在撇号。如果数据以 形式返回，那么在将值传递到时将发生FLOAT隐式转换，因为其中没有撇号，并且转换后的值只会到而不是到。VARCHARCHARINDEX1.23457e+008CHARINDEXdbo.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;

显然，此时您已经意识到邮政编码应该存储为字符串而不是数字。如果没有其他原因，只是外国（非美国）邮政编码经常包含字母。

也就是说，让我们回到你原来的问题。为什么当你有 5 个数字时你的长度显示为 5，而当你只存储 9 个数字时你的长度显示为 12。这与LEN 函数的工作方式有关。如果您查看此示例代码的 XML 执行计划：

CREATE TABLE #temp (MyFloat float, MyStr varchar(50))
INSERT INTO #temp VALUES (12345,'12345'),(123456789,'123456789')
SELECT len(MyFloat), len(MyStr) FROM #temp

你会注意到这两行：

<ScalarOperator ScalarString="len(CONVERT_IMPLICIT(varchar(23),[tempdb].[dbo].[#temp].[MyFloat],0))">
<ScalarOperator ScalarString="len([tempdb].[dbo].[#temp].[MyStr])">

您是否看到CONVERT_IMPLICIT在varchar(23)获取LEN. （据我所知LEN，实际上只适用于字符串。）

因此，让我们看一下CONVERT_IMPLICIT通过显式转换实际返回的内容。

SELECT CONVERT(varchar(23), MyFloat) FROM #temp

现在我们看到它12345转换得很干净。 123456789但是需要科学记数法。给我们1.23457e+008。长度为 12 个字符。

邮政编码作为浮点数据类型导入。我无法将列直接从 float 转换为 varchar(10)。相反，我将数据类型从浮点数转换为十进制，然后从十进制转换为 varchar(10)。现在，该列正在报告正确的邮政编码位数 (9)。现在允许在 9 位邮政编码中插入连字符。