locked
Updating rows and removing extra spaces. RRS feed

  • Question

  • Hi there,

    I am working on a SQL Server 2008 Database table that has over 1000 rows.

    I need to update rows by removing extra spaces between words. For example:-

    ColumnName

    This     is simple    Data in     a row

    This is     the    second row

    As you can see, this above rows have extra spaces in them. How do I update this column to have only a single space between words.

    Kindly reply.

    All comments and feedback are welcomed :)

    Thank you!

    Monday, February 21, 2011 9:22 AM

Answers

  • Add this function to your database:

    CREATE FUNCTION NormalizeSpaces(@string varchar(max))
    RETURNS varchar(max)
    AS
    BEGIN
    	DECLARE @Result varchar(max)
    	WHILE CHARINDEX('  ',@string) > 0 SET @string = REPLACE(@string, '  ', ' ')
    	SELECT @Result = LTRIM(RTRIM(@string))
    	RETURN @Result
    END
    

    Usage:

    SELECT dbo.NormalizeSpaces('This     is simple    Data in     a row')
    SELECT dbo.NormalizeSpaces('This is     the    second row')
    SELECT dbo.NormalizeSpaces(FieldName) FROM TableName

     

     


    Pradeep, Microsoft MVP (Visual Basic)
    http://pradeep1210.wordpress.com
    • Marked as answer by theITvideos Monday, February 21, 2011 12:20 PM
    Monday, February 21, 2011 10:04 AM
  • Have a look at something like the following...

    http://www.itjungle.com/fhg/fhg101106-story02.html


    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by theITvideos Monday, February 21, 2011 12:20 PM
    Monday, February 21, 2011 10:05 AM

All replies

  • Add this function to your database:

    CREATE FUNCTION NormalizeSpaces(@string varchar(max))
    RETURNS varchar(max)
    AS
    BEGIN
    	DECLARE @Result varchar(max)
    	WHILE CHARINDEX('  ',@string) > 0 SET @string = REPLACE(@string, '  ', ' ')
    	SELECT @Result = LTRIM(RTRIM(@string))
    	RETURN @Result
    END
    

    Usage:

    SELECT dbo.NormalizeSpaces('This     is simple    Data in     a row')
    SELECT dbo.NormalizeSpaces('This is     the    second row')
    SELECT dbo.NormalizeSpaces(FieldName) FROM TableName

     

     


    Pradeep, Microsoft MVP (Visual Basic)
    http://pradeep1210.wordpress.com
    • Marked as answer by theITvideos Monday, February 21, 2011 12:20 PM
    Monday, February 21, 2011 10:04 AM
  • Have a look at something like the following...

    http://www.itjungle.com/fhg/fhg101106-story02.html


    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by theITvideos Monday, February 21, 2011 12:20 PM
    Monday, February 21, 2011 10:05 AM
  • Thank you very much for your reply. replace function really does the trick :)

    Now in the same Column, I have some values as:

    87 02 A 52
    87 02 A 53
    87 02 A 54
    87 02 A 55

    And I want to update them to:

    87 02 52 A
    87 02 53 A
    87 02 54 A
    87 02 55 A

    As you noticed in the 1st set of values, the first 3 places are identical i.e 87 02 A and to update them to the bring the Alphabet to the last position, can we use replace function or what other function do you recommend to bring A to the last position?

    kindly reply.

    Thank you!


    Monday, February 21, 2011 12:34 PM
  • If you know that the format will always be same then you can use the Replace function:

    SELECT REPLACE('87 02 A 52', ' A', '') + ' A'
    

     


    Pradeep, Microsoft MVP (Visual Basic)
    http://pradeep1210.wordpress.com
    Monday, February 21, 2011 12:38 PM