Click here to Skip to main content
14,883,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
SQL
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
SQL
select dbo.RemoveSpaces('3-2-205,  Raja Mudliar Street,  kalasiguda,  Secunderabad') 


then its work fine but when m using function like

SQL
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
Updated 17-Jan-20 5:15am
Comments
Guirec 13-Feb-13 2:30am
   
What is the sql type of afa.address ?
[no name] 18-Feb-13 5:49am
   
its varchar

to remove multiple spaces ...
SQL
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..
SQL
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...
   
v2
METHOD 1 - WHILE LOOP AND REPLACE

By Using WHILE Loop, we can check the occurrences of double spaces, as Loop Condition.
Till the occurrence (Loop) fails, we need to alter every Double Spaces as Single Space.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
WHILE CHARINDEX('  ',@TestString) <> 0
 SET @TestString = REPLACE(@TestString,'  ',' ');
 
SELECT @TestString AS RESULT;


If we also want to remove spaces at front and end of string, then use LTRIM AND RTRIM Functions.
If our SQL Server version is higher, then we can also use TRIM Function instead of LTRIM and RTRIM.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
WHILE CHARINDEX('  ',@TestString) <> 0
 SET @TestString = REPLACE(@TestString,'  ',' ');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;


METHOD 2 - UNUSED CHARACTERS IN REPLACE

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
SELECT @TestString =
REPLACE(
    REPLACE(
        REPLACE(@TestString, ' ', ' ^')
    ,'^ ', '')
, '^', '');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;


We can also use any other character too instead of ‘^’.
Main thing is, it should not be one of the characters that is already present in input string.

METHOD 3 - ASCII NON-PRINTABLE CHARACTERS IN REPLACE

If we are not sure on what unused characters to use, then suggestion is to use ASCII Non-Printable characters instead of Unused characters like below.

DECLARE @TestString VARCHAR(200);  
SET @TestString='   Ex      ample   St  ring   '; 
 
SELECT @TestString = 
REPLACE(
    REPLACE(
            REPLACE(@TestString,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
,CHAR(17)+CHAR(18),' ');
 
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;


CONCLUSION

We can use the above methods to Remove Duplicate as well as Multiple spaces to Single space. If we consider performance, Method 3 will be better than other two methods.
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900