Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to Remove Extra Spaces in a String
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 07/24/2013 :  10:16:25  Show Profile  Reply with Quote

I am using sql server 2008.
I have a "Description"column in my database which is a string which has more than 2 spaces in between the words and the spaces are different for each record

Description1='AAAA BBBB CCCC'
Description2='AAAA BBBB CCCC'
Description1='AAAA BBBB CCCC'

I only want a single space between the words.

I tried using REPLACE but i have to use it multiple times to get the single space like hardcoding.Is there a way to write a loop such that it checks and as result we get only one space between the words?

Thanks in advance

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/24/2013 :  10:24:03  Show Profile  Reply with Quote
SQL Server has very few string manipulation functions, so you are pretty much stuck with what you described. You can nest the REPLACE statements, so you can find the largest consecutive number of spaces anywhere and nest replace that many times.

Another option, if you really want to, is to split the strings using space as the delimiter and then reassemble the split strings with a single space as the delimiter. Not too hard, but I would prefer the nested replace statement unless there was some compelling reason to use another approach.
Go to Top of Page

Flowing Fount of Yak Knowledge

8781 Posts

Posted - 07/24/2013 :  10:24:58  Show Profile  Visit webfred's Homepage  Reply with Quote
see here:

Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000