remove spaces - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

remove spaces

Author

Topic

David Redmayne
Starting Member

4 Posts

Posted - 2011-11-16 : 09:08:41

I need to remove a space from two columns in a table (SQL Server 2005,SQL Management Express)

Example: Mon 1 to Mon1

I've tried:
select
replace(tbl.colname,' ',''),
replace(tbl.colname,space(1),'')
from tbl

but this doesn't seem to work! Any suggestions please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-16 : 10:14:41

replace(tbl.colname,' ','')
should work.
What leads you to say doesn't seem to work?

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-11-16 : 10:19:21

The space (CHAR(32)) could be another character like a tab (CHAR(9)).
Try:


SELECT STUFF(tbl.colname, PATINDEX('%[^A-Z,0-9]%', tbl.colname), 1, '')
FROM tbl

David Redmayne
Starting Member

4 Posts

Posted - 2011-11-16 : 10:40:55

Thanks! After the query has run and I refresh, the data is the same!

quote:
Originally posted by webfred

replace(tbl.colname,' ','')
should work.
What leads you to say doesn't seem to work?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-16 : 10:44:18

Aha!
You are doing a SELECT only.
That means no changes happened in the table.
To make it permanent:
update table set column=replace(column,' ','') where ....

No, you're never too old to Yak'n'Roll if you're too young to die.

David Redmayne
Starting Member

4 Posts

Posted - 2011-11-16 : 10:50:01

Actually Webfred - it IS working, it's me that's not!

When then query executes a pane appears at the bottom the screen headed 'Expr1' and thr data is correct. What I need to happen is for the 'Expr1' to become tha actual data.

Thanks again

quote:
Originally posted by webfred

replace(tbl.colname,' ','')
should work.
What leads you to say doesn't seem to work?

No, you're never too old to Yak'n'Roll if you're too young to die.

David Redmayne
Starting Member

4 Posts

Posted - 2011-11-16 : 10:52:17

Thanks Webfred - you beat me to it! That works just fine!

quote:
Originally posted by webfred

Aha!
You are doing a SELECT only.
That means no changes happened in the table.
To make it permanent:
update table set column=replace(column,' ','') where ....

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-16 : 10:54:47

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources