Corr
asked on
Remove Space Between Characters In A Field
Hello,
In my first name field I have the first name followed by several spaces then the Middle initial. I need to remove all blank spaces except for one between the first name and the middle initial.
So 'John E' needs to look like 'John E'
Any help is appreciated.
Thanks,
John
In my first name field I have the first name followed by several spaces then the Middle initial. I need to remove all blank spaces except for one between the first name and the middle initial.
So 'John E' needs to look like 'John E'
Any help is appreciated.
Thanks,
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
im almost with angel on this one but i would do:
declare @rc int
set @rc=1
WHILE @rc>0
BEGIN
Update MyTable SET MyField=replace(myfield,' ',' ')
SET@rc=@@ROWCOUNT
END
by using the rowcount it saves time having to do existance checks
declare @rc int
set @rc=1
WHILE @rc>0
BEGIN
Update MyTable SET MyField=replace(myfield,' ',' ')
SET@rc=@@ROWCOUNT
END
by using the rowcount it saves time having to do existance checks
ASKER
Both Queries worked for me. AngelIII's was best for me in my situation, but gave him the majority for first response. I appreciate everyones help.
Regards,
John
Regards,
John
UPDATE YourTable
SET FirstName = LEFT(FirstName, CHARINDEX(' ', FirstName) - 1) + ' ' + RIGHT(FirstName, CHARINDEX(' ', REVERSE(FirstName)) - 1)