0

Hello i am having problems with this column data

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

How i can filter that value, what is the easy way with trim? i did my research but i find long sql statements that i dont understand if that could help is a simple error.

This is the query:

-- 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
3
  • 1
    You could maybe do select distinct REPLACE(customer_city, ' ', ' '). But what about LosAngeles with no space? Apr 12 at 10:04
  • 1
    Add a column with the name like [NormalizedCityName], then copy your values into it and: replace multiple spaces with one space; add space before capitalized letter if it is absent and the letter is not first in the value; maybe something else. Then check this column for duplicates and misprintings (for example, triple t in Manhatttan Beach). Finally you may check for rows pairs whose Levenstein distance is 1.
    – Akina
    Apr 12 at 10:39
  • 3
    this is a data quality problem. the habit of querying around bad data is not a good one to form. you should fix the underlying issues. Apr 12 at 17:24
0

As Erik said in the comments, you should fix bad data rather than trying to query around it, but if you absolutely cannot fix the data, the below query will get the distinct list of cities by replacing double spaces with a single space:

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

This is a really basic example, however, if the input data is not being validated, this may not be the only type of whitespace you're encountering causing duplication.

Prior to SQL 2017, you need to daisy chain multiple REPLACE statements to replace multiple characters. For example, this code replaces double spaces and tab characters with a single space:

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

In 2017, you can use the TRANSLATE function to swap all of the characters you're searching for with a single character, then replace that character with nothing to ensure you're finding all exact duplicates:

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

This means you don't have to repeat REPLACE for every character you want to strip, you just add the character code (+ CHAR(?)) to the TRANSLATE function and add another replacement character (#). As you can see, the TRANSLATE example replaces 4 characters for basically the same amount of code as the two-character replacement in prior versions.

2
  • hello your ans looks nice I am adding the DATABASE if you want to go trough. I have this problem. I find this when I execute your queries ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Apr 15 at 7:09
  • 1
    @rubengavidia0x you can reference the column alias in the ORDER BY clause instead, e.g. ORDER BY cities ASC. Check out this example.
    – HandyD
    Apr 15 at 22:41
0

As has already been said, correcting the data is the best way to deal with situations like this if that is possible. You could either fix it in-place, or if for some reason you need to keep the errant values (perhaps they match up to vales in another system yours is loosely coupled with, that has become dependent on these values) then perhaps by maintaining a shadow column with normalised data as Akina suggests.

If you are dealing with a large amount of data then applying a function in DISTINCT may be a bad idea because the distinct operation implies a sort which for a significant number of rows could result in an expensive spool to disk. If you have an appropriate index on customer_city the query planner might otherwise be able to use that to remove the need to sort at all. You might minimise the effect of this by performing the function on the result of the DISTINCT and then doing it again:

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

Also note sorting by the same values (the result of the function) to try avoid an extra sort after performing the DISTINCT sort & filter. Obviously for small amounts of data this is overkill and you should instead keep with the simpler query to make the code easier to understand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.