Alex Thoresen Alex Thoresen - 2 days ago 4
SQL Question

SQL distinct name pulling duplicates - already got rid of spaces

Edit: the solution provided by vax was successful, and should work for just about anyone facing a similar issue, as it replaces 4 different types of spacing, not just spaces. Thanks to all who provided help!

I have a table that gives industry names, but for one of the columns the distinct values have duplicates.

Here is the query I have:

select distinct replace(final_industry,' ','') from industryNorm


this is the results that are generated:


  1. Automotive,AerospaceandDefense,Transportation,Travel,IndustrialProducts,andInfrastructure

  2. Automotive,AerospaceandDefense,Transportation,Travel,IndustrialProducts,andInfrastructure

  3. Communications,Media&Technology

  4. ConsumerIndustriesandRetail

  5. ConsumerIndustriesandRetail

  6. Energy,Healthcare&ProcessIndustries

  7. FinancialServices

  8. FinancialServices



As you can see, many of the repeat. It seems that they still have spaces at the end, but I don't understand why, as I've run a replace function to get rid of them. I also ran a replace to put a | in the place of any space and when I do that the spaces at the end don't show up. Why is that? What can I do?

Can anyone help me out with this? Thanks!

vax vax
Answer

If you believe the problem is due to other white spaces I had a similar question that someone answered

select distinct replace(replace(replace(replace(
   final_industry
  ,char(9)/*tab*/,'')
  ,char(10)/*newline*/,'')
  ,char(13)/*carriage return*/,'')
  ,char(32)/*space*/,'')
from industryNorm