0

I have a database and a certain column has multiple spaces sometimes which need to reduced to one space, like this one for example between Condo and Alki

West Seattle. Beach Dr. Condo  Alki Beach. Seattle

Now, in the beginning I had far more and I ran the following statement and got rid of them, however a couple ones like the one mentioned above do not seem to get formatted for some reason. This is the code I am using:

UPDATE [csv].[Listings]
SET name = REPLACE(name, '  ', ' ')
WHERE name LIKE '%  %'
Rick Astley
  • 125
  • 1
  • 11
  • Please, Check this. https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql – Osama Nour May 07 '20 at 20:08
  • Two things jump to mind. 1) you may need to run it repeatedly. 2) are you sure they are spaces and not some other white space like a tab – Kevin May 07 '20 at 20:08
  • @Kevin I tried tab however, since the data I got is from airbnb, therefore might contain some special characters, I am afraid that some spaces are the characters not showing up correctly.. I have no clue what to do though.. – Rick Astley May 07 '20 at 20:09
  • @Kevin So I went to check the data in my original file and it seems that they are using a new line. – Rick Astley May 07 '20 at 20:12
  • escape character for new line is \n – Kevin May 07 '20 at 20:15
  • Does this answer your question? [Replace duplicate spaces with a single space in T-SQL](https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql) – Andrea May 11 '20 at 07:40

0 Answers0