6

I've got a data-set of people's names but the problem is I imagine when some people were typing in their names they hit the spacebar a few times too many b/c now we have this:

enter image description here

Notice how in the name column there're some names like John_Doe, John__Doe, John____Doe, etc. What would be the best way to ensure that whenever there's a _ between words, be it 1,2,3, etc. it's removed/trimmed to only 1 space so all of these records would become John_Doe.

Thoughts?

Joshua
  • 287
  • 1
  • 2
  • 12
  • 3
    ***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things like string manipulation are **highly vendor-specific** - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... – marc_s Aug 05 '13 at 19:15
  • 1
    select name, replace(replace(replace(name,' ','<>'),'><',''),'<>','_') from table – Mihai Aug 05 '13 at 19:17
  • I'm using SQL Server Management Studio. – Joshua Aug 05 '13 at 19:57
  • Possible duplicate of [Replace duplicate spaces with a single space in T-SQL](http://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql) – xpy Apr 04 '16 at 13:03

2 Answers2

13

This should do the trick

DECLARE @string varchar(100)
SET @string = 'John   Doe'

SELECT string = REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')

Replace duplicate spaces with a single space in T-SQL

Community
  • 1
  • 1
HKImpact
  • 610
  • 1
  • 9
  • 23
0

using REPLACE is really slowing the process in my opinion, especially if you have large data to search on. My suggestion is to chop the input string and use ANDs and LIKEs.

awan.soekamto
  • 9
  • 2