question

sam09 avatar image
sam09 asked

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],
updatealter-tablecharindexstring-function
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

1 Answer

·
GPO avatar image
GPO answered
Concatenated peoples' names are painfully fickle as discussed [here][1] 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; [1]: https://ask.sqlservercentral.com/questions/114687/parsing-full-name-to-first-mid-last.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.