How to remove a "space" from a name filed and move the next text characters over to another column.

We have about 12K records where name is " JOHN" or "JOHN  J" or "JOHN ", I have heard there is a simple way to find and replace the space character, however I need to move any character after the space to another Colum.

Example:
FNAME
----------
JOHN J

Needs to be
FNAME  MNAME
---------   ----------  

JOHN      J
LVL 1
sqlagent007Asked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
try this
select case when charindex(' ',fname)>0 then left(fname,charindex(' ',fname)-1)
else fname
end as fname
, case when charindex(' ',fname)>0 then right(fname, len(fname)-charindex(' ',fname))
else NULL
end as mInit
from 
--from here down is just sample data
(select 'JOHN J' as fName
union all
select 'john')Names

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.