Click here to Skip to main content
12,449,919 members (47,098 online)
Rate this:
 
Please Sign up or sign in to vote.
HEllo Everyone,

I am Using Sql Server 2005

i want remove double space or enter from the column of table for that i am using following code

For that I am using RemoveSpaces Function
the function Contain following code
CREATE FUNCTION RemoveSpaces  
 
 (@InputString VARCHAR(1024))  
 
  RETURNS VARCHAR(1024)  
  
  AS  
  
  BEGIN  
  
    WHILE CHARINDEX('  ',@InputString) > 0  -- Checking for double spaces  
  
      SET @InputString =  
  
        REPLACE(REPLACE(REPLACE(ISNULL( @InputString, ''), CHAR(13), ''), CHAR(10), ' '),'  ',' ') -- Replace 2 spaces with 1 space  

    RETURN @InputString  
  
  END

Now If m using this function like
select dbo.RemoveSpaces('3-2-205,  Raja Mudliar Street,  kalasiguda,  Secunderabad') 

then its work fine but when m using function like

select dbo.RemoveSpaces(afa.address) as addr  from table1
then its not working...

Can u tell me where is the problem?

Note: Avoid Bad English
Thanks
Chetan V
Posted 12-Feb-13 20:10pm
Comments
Guirec Le Bars 13-Feb-13 2:30am
   
What is the sql type of afa.address ?
chetan virkar 18-Feb-13 5:49am
   
its varchar

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

to remove multiple spaces ...
DECLARE @s VARCHAR(MAX) = '221B    BAKER       STREET';
   SELECT REPLACE(
           REPLACE(
              REPLACE(
                   LTRIM(RTRIM(@s))
               ,'  ',' '+CHAR(7))
           ,CHAR(7)+' ','')
       ,CHAR(7),'') AS CleanString ;
refer below link...

http://davidbrycehoward.com/archive/2011/05/more-recursionremoving-multiple-spaces/[^]

to remove newline character..
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds refer below...

http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/[^]

hope this will help you...
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100