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.
| Author |
Topic |
|
peace
Constraint Violating Yak Guru
420 Posts |
 Posted - 2013-10-17 : 09:09:56
|
| I have data with double space.Sample:John Johnson Marry KittyLoppes GinnaHow can I replace only 1 space?I tried as below:select case when Name like '% %' then replace(name,'','') end? |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-10-17 : 09:49:58
|
| [CODE]DECLARE @Temp TABLE (ID int, Name VARCHAR(20))INSERT INTO @Temp VALUES (1, 'John Doe'),(2, 'Jane Doe'),(3, 'Jim Jones');SELECT ID, REPLACE(Name,' ',' ') FROM @Temp;SELECT *, LEN(NAME) FROM @Temp;SELECT ID, REPLACE(Name,' ',' '), LEN(REPLACE(Name,' ',' ')) FROM @Temp;[/CODE] |
 |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-10-17 : 10:35:22
|
| what if the double space at last of the data?johnson mary[space][space] |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-17 : 10:57:55
|
quote:
Originally posted by peace
what if the double space at last of the data?johnson mary[space][space]
Do you still want to replace the two trailing spaces with a single space, or do you want to get rid of both of those? If the former, Mumu's code will still work. If you want to get rid of trailing spaces, use RTRIM.SELECT ID, RTRIM(REPLACE(Name,' ',' ')) FROM @Temp; |
|
|
|
|
|
|
|
|
replace