question

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