Códigos postais de 9 dígitos estão relatando LEN de 12

Os códigos postais são strings, não números. Alguns deles têm 1 ou até 2 (mas não mais que 2) zeros à esquerda . O tipo de dados na tabela de importação deve VARCHAR(10)conter códigos postais de 5 dígitos e 9 dígitos + hífen. Mesmo que você nunca precise armazenar códigos postais de outros países e mesmo que os valores tenham apenas dígitos numéricos (ou seja, 0 - 9), esses dados ainda são dados de string, assim como números de telefone.

Com base na consulta de importação mostrada em sua outra pergunta ( automatizar a importação e exportação do processo EXCEL -> SQL SERVER -> EXCEL sem usar o 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$]');

Eu sugeriria não confiar na SELECT INTOconstrução para criar a XLS_IMPORTtabela, mas criar a(s) tabela(s) de importação manualmente e, em seguida, usar a INSERT INTO ... SELECT FROM OPENROWSET()construção. Isso permitiria que você fizesse o seguinte para melhorar essa situação:

1. Crie o ZipCodecampo comoVARCHAR(10)
2. Converta os valores no caminho usando a função STR que, para um FLOATvalor inicial de 432013256, retornará 432013256em vez de 4.32013e+008(que é o que você obtém quando converte para VARCHAR).

3. Corrija quaisquer zeros iniciais ausentes usando algo como o seguinte:

   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
   

Exemplo:

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;

Retorna:

032013256

Idealmente, você corrigiria a definição da coluna na planilha para ser uma string. Mas mesmo se você fizer isso, ainda pode ser uma boa ideia manter esse código por perto.

Meu objetivo final é simplesmente fazer com que o código postal de 9 dígitos tenha um hífen no meio.

Com esse objetivo em mente, o TVF embutido a seguir pode ser usado para converter o FLOATvalor em VARCHAR, e para adicionar o hífen para valores ZIP + 4.

Código para 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;

Teste:

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

Retorna:

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

Para ficar mais claro sobre o que está sendo sugerido, o seguinte mostra todas as sugestões mencionadas acima reunidas:

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;

Algumas das planilhas estão "cientes" da existência de zeros à esquerda e, portanto, prefixam o campo no Excel com um único apóstrofo para que o Excel trate o valor como uma string em vez de numérico (por exemplo, '01234). Nesse caso, você pode usar a REPLACEfunção para remover esse apóstrofo.

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

Porém não dá para usar REPLACEem todas as planilhas pois as planilhas que tem a ZIPcoluna como numérica farão um a CONVERT_IMPLICITmedida que passa o valor para a REPLACEfunção e o valor convertido ficará em notação científica (ex 1.23457e+008. ). Portanto, se você não puder ter certeza de qual tipo de dados será retornado por OPENROWSET, poderá usar IIF(ou CASEse estiver usando uma versão do SQL Server anterior a 2012) e CHARINDEXtestar a presença de um apóstrofo. Se os dados estiverem sendo retornados como FLOAT, a conversão implícita para VARCHARisso ocorrerá ao passar os valores para CHARINDEXnão será um problema, pois não há apóstrofo 1.23457e+008e o valor convertido será apenas para CHARINDEXe não para 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;

Obviamente, neste ponto, você percebeu que os códigos postais devem ser armazenados como strings, não como números. Pelo menos pelo fato de os códigos postais estrangeiros (fora dos EUA) frequentemente conterem letras.

Dito isso, vamos voltar à sua pergunta original. Por que seu comprimento mostrava 5 quando você tinha 5 dígitos e 12 quando armazenava apenas 9 dígitos? Isso tem a ver com a forma como a função LEN funciona . Se você observar o plano de execução XML para este código de amostra:

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

Você notará estas duas linhas:

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

Você vê como nos bastidores há um CONVERT_IMPLICITpara converter o valor float em a varchar(23)antes de obter o arquivo LEN. (Pelo que posso dizerLEN , na verdade, só funciona em strings.)

Então, vamos dar uma olhada no que CONVERT_IMPLICITrealmente está retornando ao fazer uma conversão explícita.

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

E agora vemos que 12345converte de forma limpa. 123456789notação científica necessária no entanto. Dando-nos 1.23457e+008. Que tem 12 caracteres.

O código postal foi importado como um tipo de dados float. Não consegui converter a coluna diretamente de float para varchar(10). Em vez disso, converti o tipo de dados de float para decimal e, em seguida, de decimal para varchar(10). Agora a coluna está informando o número correto de dígitos do código postal (9). Isso agora permite inserir um hífen nos códigos postais de 9 dígitos.