Removing blank spaces in records.

  • Hi all,

    I have a problem where I have imported quite a lot of data from an Excel file, and some of this data contains fields that have a lot of blank spaces inbetween characters. For example:

    "I live in the town which is near this town"

    Is there a query I can use to get rid of this? I guess if I knew the exact amount of space I could do a REPLACE(), but I don't know the exact spaces between characters.

    Hope you can help.

    Thanks

    Tryst

  • no need to

    REPLACE(MyData,' ','') will replace/remove every space.

  • If your data in a column in table try this :

     

    update [Name of Table] set name [Name of Column] = REPLACE ( [Name of Column] ,' ','')

    This will remove any spaces between words in all column (Named column above)

     

  • Those examples will remove all spaces (obviously) - which might well be what you want (it's not clear to me).

    The example below will remove duplicate spaces - just in case that was what you wanted (unlikely).

    --data

    declare @t table (v varchar(8000))

    insert @t select 'I live          in the town which is                 near this town'

    --calculation

    declare @UnusedChar char(1)

    set @UnusedChar = '¬' --or whatever - use char(x) if necessary

    update @t set v = replace(replace(replace(v, ' ', ' ' + @UnusedChar),

        @UnusedChar + ' ', ''), @UnusedChar, '')

    select * from @t

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Hi all,

    and thanks for the replies.

    If I had gone with the first two replies, that would have made my example, which was...

    "I live in the town which is near this town"

    ...in to...

    "Iliveinthetownwhichisnearthistown"

    ...which isn't what I wanted.

    RyanRandall, I will give your code a try.

    Thanks.

    Tryst

  • Hi Tryst, 
    Please try this one..

    update [Name of Table] set name [Name of Column] =REPLACE(REPLACE(REPLACE(assessors_parcel_number, ' ', '*^'), '^*', ''), '*^', ' ');

    Thanks!

    Jessin

  • jessinthomas012 - Thursday, June 14, 2018 1:53 AM

    Hi Tryst, 
    Please try this one..

    update [Name of Table] set name [Name of Column] =REPLACE(REPLACE(REPLACE(assessors_parcel_number, ' ', '*^'), '^*', ''), '*^', ' ');

    Thanks!

    Jessin

    Thanks for stepping up to the plate with that post, Jessin. It brings up a common problem and a solution that I used to think that was good...  so much so that I wrote an article about the method a while back.  That's when I learned of a method from the discussion that followed that's nearly an order of magnitude faster proving that this is an incredible community.  Here's the article along with the note at the beginning that takes you to the post in the discussion that changed it all.  And, the discussion has some remarkable tests done by some remarkable people.
    http://www.sqlservercentral.com/articles/T-SQL/68378/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply