November 1, 2006 at 2:29 pm
Hello all.
I am trying to clean up a horrendously dirty database. MyCustomers table contains CompanyName, FName, LName. Some time ago, data was imported improperly from an older database, and I now have in the CompanyName field
ABC Company
Joe Smith
ACME Bells and Whistles
I realize there is NO way to get this data completely clean programmatically, but I can make a good deal of headway if I can return all rows where CompanyName contains exactly ONE space. I haven't been able to come up with a query or function for this. Can anybody help me out?
Thanks.
Mike
November 2, 2006 at 1:54 am
select substring(CompanyName , CHARINDEX(' ',CompanyName)+1, len(CompanyName)), CompanyName from MyCustomers
where CHARINDEX(' ',substring(CompanyName, CHARINDEX(' ',CompanyName)+1, len(CompanyName)))=0
i hope this help.
November 2, 2006 at 6:35 pm
This should also work asuming you don't care about the trailing blanks.
select
CompanyName
from MyCustomers
where len(CompanyName) = len(replace(CompanyName ,' ','')) + 1 -- (Change this number to check different number of spaces)
if you care about trailing blanks then use this.
select
CompanyName
from MyCustomers
where len(CompanyName + 'x') = len(replace(CompanyName + 'x' ,' ','')) + 1 -- (Change this number to check different number of spaces)
November 3, 2006 at 11:23 am
Bimal, your function worked like a charm. Thanks for the help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply