[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2008-11-12
Medium Priority
Last Modified: 2012-05-05
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.


Needs to be
---------   ----------  

JOHN      J
Question by:sqlagent007
1 Comment
LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 22943540
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 here down is just sample data
(select 'JOHN J' as fName
union all
select 'john')Names

Open in new window


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question