SQL Server Performance

Remove spaces in string

Discussion in 'General Developer Questions' started by Lilupa, Mar 14, 2006.

  1. Lilupa New Member

    Hello,
    I want to remove spaces in a char field, but not leading or trailing spaces. These spaces included in between the characters. Please help me to remove it using T-SQL.

    Thanks

    Lilupa
  2. Madhivanan Moderator

    Why do you want to do this?

    Select cast(replace(charcol,' ','') as char(10)) from yourTable

    You can set the value according to the column definition

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

    Hm, to be honest I don't get what you're after here and why this needs to be in T-SQL. Since you haven't provided sample data, here's an approach assuming the most basic case.


    DECLARE @a CHAR(10)
    SET @a = ' a b '
    SELECT @a
    , STUFF(@a, PATINDEX('%[a-Z]%', @a)+1, LEN(@a)-PATINDEX('%[a-Z]%', REVERSE(@a))-1, '')



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  4. Lilupa New Member

    Dear Madhivanan,
    Thank you very much. I did it and it is working properly. Thank you again.

    Lilupa.
  5. Madhivanan Moderator

    What about Frank's code?

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    You said, you don't want to remove neither leading nor trailing spaces. A simple REPLACE() removes all occurences. If that's now okay for you, it's even better. However, it doesn't match your description. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  7. maksimmm New Member

    quote:Originally posted by Madhivanan

    Why do you want to do this?

    Select cast(replace(charcol,' ','') as char(10)) from yourTable

    You can set the value according to the column definition

    Madhivanan

    Failing to plan is Planning to fail

    Really great solution, but it removes leading spaces

    Maksim
  8. Adriaan New Member

    Maksim,

    It's funny you should mention that - please check Frank's first post on this thread ...

Share This Page