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 |
|
freedg
Starting Member
2 Posts |
 Posted - 2012-05-28 : 19:32:22
|
| I have a column "FirstName" that has bad data. It contains "Michael A." instead of "Michael". The are 1400 rows of similar data. The first name is always followed by a space but the first names are variable lengths. How can I fix this? SQL server 2008 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-28 : 22:40:36
|
how do you want to fix this ? what is the rule ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-29 : 01:27:17
|
if you just want only part below first space you can use logic likeSELECT LEFT(Name,CHARINDEX(' ',Name + ' ')-1) AS FirstName FROM Table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
freedg
Starting Member
2 Posts |
Posted - 2012-05-29 : 07:55:54
|
| thanks Visakh16That is exactly what I needed!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-29 : 12:02:51
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-05-29 : 12:52:14
|
| What's this give you?LEN(FirstName)-LEN(REPLACE(FirstName),' ','') AS FirstName_Spaces, COUNT(*) AS Spaces_RowsFROM TableGROUP BY LEN(FirstName)-LEN(REPLACE(FirstName),' ','') |
|
|
|
|
|
|
bad data to fix