x

Alter the contents of columns in a table

I have a column called FIRST_NAME (that currently contains full name). I have another column called LAST_NAME (last name). I need to alter the FIRST_NAME column so that it contains the proper first name. The last name is to go to the LAST_NAME column. For example:

If FIRST_NAME contains 'NENO S ASBERRY' then it should contain only NENO. 'S ASBERRY' should go to the LAST_NAME column.

 FIRST_NAME          LAST_NAME   
 CAROL ARPS          ARPS        
 NENO S ASBERRY      S ASBERRY

I want it changed to this:

 FIRST_NAME                    LAST_NAME          
 -------------------        ----------------------        
 CAROL                       ARPS                   
 NENO                        S ASBERRY

This query will be there in procedure:

 SUBSTRING(data,36,30) as  [FIRSTNAME],
 SUBSTRING(data,66,21) as  [LASTNAME],


more ▼

asked Jan 08, 2016 at 08:53 PM in Default

avatar image

sam09
18 1 5

I don't see a question. What do you want to know?

Jan 08, 2016 at 09:49 PM Alvin Ramard
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Concatenated peoples' names are painfully fickle as discussed here

I'd also add that you probably don't want to overwrite your source data (currently in the column called FIRST_NAME) because you have nothing to refer back to later on in you suspect a problem. Having said all of that, this will probably do what you want it to do:

 IF OBJECT_ID('tempdb..#peoples_names') IS NOT NULL DROP TABLE #peoples_names;
 
 --set up a table to match the OP's.
 SELECT cast('asfdfd ddfg' as varchar(100)) as FIRST_NAME, cast('' as varchar(100)) as LAST_NAME INTO #peoples_names UNION ALL 
 SELECT 'asfd ddfasdg'  as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'asfd ddftyg'   as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'ashd xcvddfg'  as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'asyd ddfererg' as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'atsd ddyuuyfg' as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'astd ddfujujg' as FIRST_NAME, '' as LAST_NAME UNION ALL 
 SELECT 'arsd ddfghg'   as FIRST_NAME, '' as LAST_NAME;
 
 --rename the FIRST_NAME column to SOURCE_NAME (assumes there are no references to it)
 EXEC tempdb.sys.sp_rename '#peoples_names.FIRST_NAME', 'SOURCE_NAME', 'COLUMN';
 
 --create a new column to hold the FIRSTNAME
 ALTER TABLE #peoples_names ADD FIRST_NAME varchar(200) NULL;
 
 --Finally answer the OP's question.
 UPDATE  #peoples_names
 SET     FIRST_NAME = LEFT(SOURCE_NAME,CHARINDEX(' ',SOURCE_NAME,1)-1)
         ,LAST_NAME = SUBSTRING(SOURCE_NAME,CHARINDEX(' ',SOURCE_NAME,1)+1,len(SOURCE_NAME));
 
 SELECT SOURCE_NAME,FIRST_NAME,LAST_NAME FROM #peoples_names;


more ▼

answered Jan 09, 2016 at 06:26 AM

avatar image

GPO
4.9k 42 51 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x175
x14
x12
x12

asked: Jan 08, 2016 at 08:53 PM

Seen: 340 times

Last Updated: Jan 09, 2016 at 06:27 AM

Copyright 2018 Redgate Software. Privacy Policy