Início / dba / Perguntas / 289665
Accepted
rubengavidia0x
Asked: 2021-04-13 01:56:53 +0800 CST

SELECT DISTINCT em dados de espaço extra de uma única coluna (sem duplicatas)

  • 772

Olá estou tendo problemas com os dados desta coluna

cities
Charlotte
Cincinnati
Cleves
Columbus
Fairfield
Fresno
Los  Angeles -- HERE IS THE PROBLEM
Los Angeles
Madison
Manhatttan  Beach
New York
Orange
Palo Alto
Phoenix
Sacramento
San Francisco
Takoma  Park -- maybe this city must have problems also
Tarrytown
Valencia
Washington

Como posso filtrar esse valor, qual é a maneira mais fácil com o trim? eu fiz minha pesquisa, mas encontro instruções sql longas que não entendo se isso puder ajudar é um erro simples.

Esta é a consulta:

-- 3. Write a query that will list all the cities that have customers with a heading of Cities. Only
-- list each city once (no duplicates) and sort in descending alphabetical order.

select distinct customer_city as cities
FROM customers
ORDER BY customer_city ASC

-- left Los  Angeles
sql-server duplication

2 respostas

  1. Best Answer

    Como Erik disse nos comentários, você deve corrigir dados ruins em vez de tentar consultá-los, mas se você absolutamente não puder corrigir os dados, a consulta abaixo obterá a lista distinta de cidades substituindo espaços duplos por um único espaço:

    SELECT DISTINCT REPLACE(customer_city, '  ', ' ') as cities
FROM customers
ORDER BY customer_city ASC

    Este é um exemplo realmente básico, no entanto, se os dados de entrada não estiverem sendo validados, esse pode não ser o único tipo de espaço em branco que você está encontrando causando duplicação.

    Antes do SQL 2017, você precisa encadear várias instruções REPLACE para substituir vários caracteres. Por exemplo, este código substitui espaços duplos e caracteres de tabulação por um único espaço:

    SELECT DISTINCT REPLACE(REPLACE(customer_city, '  ', ' '), CHAR(9), ' ') as cities
FROM customers
ORDER BY customer_city ASC

    Em 2017, você pode usar a função TRANSLATE para trocar todos os caracteres que está procurando por um único caractere e substituir esse caractere por nada para garantir que encontre todas as duplicatas exatas:

    SELECT DISTINCT REPLACE(TRANSLATE(customer_city, CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32), '####'), '#', '') as cities
FROM customers
ORDER BY customer_city ASC

    Isso significa que você não precisa repetir REPLACE para cada caractere que deseja remover, basta adicionar o código do caractere ( + CHAR(?) ) à função TRANSLATE e adicionar outro caractere de substituição (#). Como você pode ver, o exemplo TRANSLATE substitui 4 caracteres por basicamente a mesma quantidade de código que a substituição de dois caracteres nas versões anteriores.

    • 1

  2. Como já foi dito, corrigir os dados é a melhor maneira de lidar com situações como essa, se possível. Você pode consertá-lo no local ou se, por algum motivo, precisar manter os valores errados (talvez eles correspondam aos valores em outro sistema ao qual o seu está fracamente acoplado, que se tornou dependente desses valores), talvez mantendo um coluna de sombra com dados normalizados como sugere Akina.

    Se você estiver lidando com uma grande quantidade de dados, aplicar uma função em DISTINCTpode ser uma má ideia, porque a operação distinta implica uma classificação que, para um número significativo de linhas, pode resultar em um caro spool para o disco. Se você tiver um índice apropriado no customer_cityplanejador de consulta, poderá usá-lo para remover a necessidade de classificar. Você pode minimizar o efeito disso executando a função no resultado do DISTINCTe depois fazendo novamente:

      SELECT DISTINCT 
         FunctionToNormaliseCity(customer_city)
    FROM (SELECT DISTINCT customer_city FROM customers) AS subq
ORDER BY FunctionToNormaliseCity(customer_city)

    Observe também a classificação pelos mesmos valores (o resultado da função) para tentar evitar uma classificação extra após executar a DISTINCTclassificação e o filtro. Obviamente, para pequenas quantidades de dados, isso é um exagero e, em vez disso, você deve manter a consulta mais simples para tornar o código mais fácil de entender.

    • 1

relate perguntas