SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AnupamaB96
Starting Member

India
1 Posts

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

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

3760 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

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 07/24/2013 :  10:24:58  Show Profile  Visit webfred's Homepage  Reply with Quote
see here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162393


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000