Quer um meio sensato e eficiente de classificar números em strings como números reais? Considere votar na minha sugestão do Microsoft Connect: suporte "classificação natural" / DIGITSASNUMBERS como uma opção de agrupamento

Não há meios integrados e fáceis de fazer isso, mas aqui está uma possibilidade:

Normalize as strings reformatando-as em segmentos de comprimento fixo:

• Crie uma coluna de classificação do tipo VARCHAR(50) COLLATE Latin1_General_100_BIN2. O comprimento máximo de 50 pode precisar ser ajustado com base no número máximo de segmentos e seus potenciais comprimentos máximos.
• Embora a normalização possa ser feita na camada do aplicativo com mais eficiência, lidar com isso no banco de dados usando um UDF T-SQL permitiria colocar o UDF escalar em um AFTER [or FOR] INSERT, UPDATETrigger de modo que você tenha a garantia de definir corretamente o valor para todos os registros, mesmo aqueles entrando por meio de consultas ad hoc etc. É claro que esse UDF escalar também pode ser manipulado por SQLCLR, mas precisaria ser testado para determinar qual deles era realmente mais eficiente. ^**
• A UDF (independentemente de estar em T-SQL ou SQLCLR) deve:
  • Processe um número desconhecido de segmentos lendo cada caractere e parando quando o tipo mudar de alfa para numérico ou numérico para alfa.
  • Para cada segmento, ele deve retornar uma string de comprimento fixo definida com o máximo possível de caracteres/dígitos de qualquer segmento (ou talvez máximo + 1 ou 2 para contabilizar o crescimento futuro).
  • Os segmentos alfa devem ser justificados à esquerda e preenchidos à direita com espaços.
  • Os segmentos numéricos devem ser justificados à direita e preenchidos com zeros à esquerda.
  • Se os caracteres alfabéticos puderem vir como letras maiúsculas e minúsculas, mas a ordenação precisar não diferenciar maiúsculas de minúsculas, aplique a UPPER()função ao resultado final de todos os segmentos (para que isso precise ser feito apenas uma vez e não por segmento). Isso permitirá uma classificação adequada, dada a ordenação binária da coluna de classificação.
• Crie um AFTER INSERT, UPDATETrigger na tabela que chama o UDF para definir a coluna de classificação. Para melhorar o desempenho, use a UPDATE()função para determinar se esta coluna de código está mesmo na SETcláusula da UPDATEinstrução (simplesmente RETURNse for falsa) e, em seguida, junte as pseudotabelas e na coluna de código para processar apenas as linhas que tiverem alterações no valor do INSERTEDcódigo DELETED. Certifique-se de especificar COLLATE Latin1_General_100_BIN2essa condição JOIN para garantir a precisão ao determinar se há uma alteração.
• Crie um índice na nova coluna de classificação.

Exemplo:

P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

Nesta abordagem, você pode classificar por meio de:

ORDER BY tbl.SortColumn

E você pode fazer filtragem de intervalo via:

WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

ou:

DECLARE @RangeStart VARCHAR(50),
        @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
       @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

Tanto o ORDER BYquanto o WHEREfiltro devem usar a ordenação binária definida para SortColumndevido à precedência de ordenação.

As comparações de igualdade ainda seriam feitas na coluna de valor original.

Outros pensamentos:

• Use um SQLCLR UDT. Isso pode funcionar, embora não esteja claro se apresenta um ganho líquido em comparação com a abordagem descrita acima.

  Sim, um SQLCLR UDT pode ter seus operadores de comparação substituídos por algoritmos personalizados. Isso lida com situações em que o valor está sendo comparado a outro valor que já é do mesmo tipo personalizado ou a um que precisa ser convertido implicitamente. Isso deve manipular o filtro de intervalo em uma WHEREcondição.

  Com relação à classificação do UDT como um tipo de coluna regular (não uma coluna computada), isso só é possível se o UDT for "ordenado por bytes". Ser "ordenado por bytes" significa que a representação binária do UDT (que pode ser definido no UDT) é classificada naturalmente na ordem apropriada. Supondo que a representação binária seja tratada de forma semelhante à abordagem descrita acima para a coluna VARCHAR(50) que possui segmentos de comprimento fixo que são preenchidos, isso se qualificaria. Ou, se não fosse fácil garantir que a representação binária seria naturalmente ordenada da maneira adequada, você poderia expor um método ou propriedade do UDT que gera um valor que seria ordenado corretamente e, em seguida, criar uma PERSISTEDcoluna computada nessa método ou propriedade. O método precisa ser determinístico e marcado como IsDeterministic = true.

  Os benefícios dessa abordagem são:

  • Não há necessidade de um campo "valor original".
  • Não há necessidade de chamar uma UDF para inserir os dados ou comparar valores. Supondo que o Parsemétodo do UDT receba o P7B18valor e o converta, você poderá simplesmente inserir os valores naturalmente como P7B18. E com o método de conversão implícito definido no UDT, a condição WHERE também permitiria usar simplesmente P7B18`.

  As consequências desta abordagem são:

  • Simplesmente selecionar o campo retornará a representação binária, se estiver usando o UDT ordenado por byte como o tipo de dados da coluna. Ou, se estiver usando uma PERSISTEDcoluna computada em uma propriedade ou método do UDT, você obterá a representação retornada pela propriedade ou método. Se você deseja o P7B18valor original, precisa chamar um método ou propriedade do UDT que é codificado para retornar essa representação. Como você precisa substituir o ToStringmétodo de qualquer maneira, esse é um bom candidato para fornecer isso.

  • Não está claro (pelo menos para mim agora, já que não testei esta parte) como seria fácil/difícil fazer alterações na representação binária. A alteração da representação armazenada e classificável pode exigir a eliminação e a adição novamente do campo. Além disso, descartar o Assembly contendo o UDT falharia se usado de qualquer maneira, portanto, você deve se certificar de que não haja mais nada no Assembly além deste UDT. Você pode ALTER ASSEMBLYsubstituir a definição, mas há algumas restrições quanto a isso.

    Por outro lado, o VARCHAR()campo são dados desconectados do algoritmo, portanto, exigiria apenas a atualização da coluna. E se houver dezenas de milhões de linhas (ou mais), isso poderá ser feito em uma abordagem em lote.

• Implemente a biblioteca ICU que realmente permite fazer essa classificação alfanumérica. Embora altamente funcional, a biblioteca vem em apenas duas linguagens: C/C++ e Java. O que significa que você pode precisar fazer alguns ajustes para fazê-lo funcionar no Visual C++, ou há a chance de que o código Java possa ser convertido em MSIL usando IKVM . Há um ou dois projetos paralelos .NET vinculados nesse site que fornecem uma interface COM que pode ser acessada em código gerenciado, mas acredito que eles não sejam atualizados há algum tempo e não os testei. A melhor aposta aqui seria lidar com isso na camada do aplicativo com o objetivo de gerar chaves de classificação. As chaves de classificação seriam salvas em uma nova coluna de classificação.

  Esta pode não ser a abordagem mais prática. No entanto, ainda é muito legal que tal habilidade exista. Eu forneci um passo a passo mais detalhado de um exemplo disso na seguinte resposta:

  Existe um agrupamento para classificar as seguintes strings na seguinte ordem 1,2,3,6,10,10A,10B,11?

  Mas o padrão tratado nessa questão é um pouco mais simples. Para um exemplo mostrando que o tipo de padrão tratado nesta questão também funciona, vá para a página a seguir:

  Demonstração de agrupamento de UTI

  Em "Configurações", defina a opção "numérica" ​​como "ativada" e todas as outras devem ser definidas como "padrão". Em seguida, à direita do botão "sort", desmarque a opção "diff strengths" e marque a opção "sort keys". Em seguida, substitua a lista de itens na área de texto "Input" pela seguinte lista:

  P12B22
  P7B18
  P12B3
  as456456hgjg6786867
  P7Bb19
  P7BA19
  P7BB19
  P007B18
  P7Bb20
  P7Bb19z23
  

  Clique no botão "classificar". A área de texto "Saída" deve exibir o seguinte:

  as456456hgjg6786867
      29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
  P7B18
      47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
  P007B18
      47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
  P7BA19
      47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
  P7Bb19
      47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
  P7BB19
      47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
  P7Bb19z23
      47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
  P7Bb20
      47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
  P12B3
      47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
  P12B22
      47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .
  

  Observe que as chaves de classificação são estruturadas em vários campos, separados por vírgulas. Cada campo precisa ser classificado de forma independente, de modo que apresenta outro pequeno problema para resolver se precisar implementar isso no SQL Server.

^** Se houver alguma preocupação com o desempenho em relação ao uso de funções definidas pelo usuário, observe que as abordagens propostas fazem uso mínimo delas. Na verdade, o principal motivo para armazenar o valor normalizado era evitar chamar uma UDF para cada linha de cada consulta. Na abordagem primária, o UDF é usado para definir o valor de SortColumn, e isso só é feito por INSERTmeio UPDATEdo Trigger. Selecionar valores é muito mais comum do que inserir e atualizar, e alguns valores nunca são atualizados. Para cada SELECTconsulta que usa o SortColumnfiltro for a range na WHEREcláusula, a UDF é necessária apenas uma vez para cada um dos valores range_start e range_end para obter os valores normalizados; a UDF não é chamada por linha.

Com relação ao UDT, o uso é realmente o mesmo do UDF escalar. Ou seja, inserir e atualizar chamaria o método de normalização uma vez por cada linha para definir o valor. Em seguida, o método de normalização seria chamado uma vez por consulta para cada range_start e range_value em um filtro de intervalo, mas não por linha.

Um ponto a favor de lidar com a normalização inteiramente em um SQLCLR UDF é que, dado que ele não está fazendo nenhum acesso a dados e é determinístico, se estiver marcado como IsDeterministic = true, poderá participar de planos paralelos (o que pode ajudar as operações INSERTe UPDATE), enquanto um O T-SQL UDF impedirá que um plano paralelo seja usado.