SQL Server Performance Forum – Threads Archive

Remove spaces in string

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

---

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

---

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)

---

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

---

What about Frank’s code? Madhivanan Failing to plan is Planning to fail

---

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

---

Really great solution, but it removes leading spaces Maksim

---

Maksim, It’s funny you should mention that – please check Frank’s first post on this thread …

---

]]>