Removing a double space in the middle of a name

  • michael.leach2015

    SSCommitted

    Points: 1584

    Suppose I had a list of names and one of the names was Kathy  Harrison.  Notice the double space after the first name.  In Excel there is a function called TRIM which could remove extraneous spaces.  I am trying to find something like that in SQL.  As far as I know, in SQL, TRIM is used for characters other than spaces.  The LTRIM and RTRIM functions are used for leading and trailing spaces.

    So how would you remove a double or triple space between a first and last name.

  • pietlinden

    SSC Guru

    Points: 62772

    use REPLACE

  • Phil Parkin

    SSC Guru

    Points: 244441

    michael.leach2015 wrote:

    As far as I know, in SQL, TRIM is used for characters other than spaces.

    Oh no it isn't. TRIM() is the equivalent of LTRIM(RTRIM()) and thus removes leading and trailing spaces.

    But to replace an arbitrary number of multiple consecutive spaces with a single space, you can use the infamous triple REPLACE trick:

    DECLARE @SomeText VARCHAR(500) = 'this   text  has     random multiple                spaces';

    SELECT Original = @SomeText
    ,Fixed = REPLACE(REPLACE(REPLACE(@SomeText, ' ', '<>'), '><', ''), '<>', ' ');

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • pietlinden

    SSC Guru

    Points: 62772

    Phil,

    I got the REPLACE stuff to work consistently correctly only if I looped (yep, dirty word) until all the double spaces were replaced. For example, if the input string had a triple-space in it, and I replaced only double with single, I still had a double-space left in my final string.  Am I just doing it wrong?

    If I did something horrid like use a WHILE loop and tested for the existence of a double-space, it worked a champ. Is there an easy way around this?

    thanks (and sorry for sort of hijacking the thread).

  • Phil Parkin

    SSC Guru

    Points: 244441

    pietlinden wrote:

    Am I just doing it wrong?

    I think so! If you try plugging a string containing a triple space into my example above, you will see that it gets condensed to a single space.

    Capture

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • @Taps

    SSC Eights!

    Points: 863

    This is so cool !

    Thanks for sharing

  • Jeff Moden

    SSC Guru

    Points: 996463

    The most efficient method for reducing any number of spaces up to 8000 that I've ever seen is as follows...

     SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    SomeStringColumn COLLATE LATIN1_GENERAL_BIN,
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    ,' ',' ')
    FROM dbo.SomeTable
    ;

    --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)
    Forum FAQ

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

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