Click here to Skip to main content
11,409,450 members (63,520 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 21:10pm
Comments
Guirec Le Bars at 13-Feb-13 2:30am
   
What is the sql type of afa.address ?
chetan virkar at 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
0 Sergey Alexandrovich Kryukov 8,676
1 OriginalGriff 6,885
2 Maciej Los 3,322
3 Abhinav S 3,238
4 Peter Leow 3,034


Advertise | Privacy | Mobile
Web04 | 2.8.150414.5 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2015
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