Unstring name into new table 1

[Mbroad]

Hi--I need to insert the name fields from table 1 to table 2. The name field in table 1 is a name like "Jones Marvin" (full name). The name fields on table 2 are first-name and last-name. How can I unstring the complete name into separate name fields?
Thanks,
Mark

Government Coder Mark

 

[SQLDenis]

lookup CHARINDEX and PATINDEX in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mbroad]

Is there a method I can use within the SQL statement itself? Thanks---

Government Coder Mark

 

[SQLDenis]

yes there is a method, like I said before lookup CHARINDEX and PATINDEX in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mbroad]

Thank you for your response. Sorry to be picky, but I do not need to find the starting position of a specific string. What I need to do is to be able to unstring the first & last-name from one field into two separate fields. Perhaps I am misunderstanding, but I don't see how PATINDEX or CHARINDEX does this.
thank you

Government Coder Mark

 

[SQLDenis]

example


declare @name varchar(50)
select @name ='Bill Gates'
select LEFT(@name,CHARINDEX(' ',@name)),
SUBSTRING(@name,CHARINDEX(' ',@name), (Len(@name) -CHARINDEX(' ',@name))+1)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

you can also do left twice with reverse in the second part

declare @name varchar(50)
select @name ='Bill Gates'
select LEFT(@name,CHARINDEX(' ',@name)-1) as FirstName,
REVERSE(LEFT(REVERSE(@name),CHARINDEX(' ',@name))) as LastName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mbroad]

SQLDEnis: Thank you very much. I am an SQL novice so I can't follow how to implement this for my particular process. Your example uses a hard-coded name where my process will read a file containing a name. Can you explain what each line is doing? Thanks very much

Government Coder Mark

 

[SQLDenis]

Assuming your column name is FullName use this

select LEFT(FullName,CHARINDEX(' ',FullName)),
SUBSTRING(FullName,CHARINDEX(' ',FullName), (Len(FullName) -CHARINDEX(' ',FullName))+1)
From YourTable

or you can take this approach using Parsename

declare @name varchar(50)
select @name ='Bill Gates'
select PARSENAME(REPLACE(@name,' ','.'),2) as FirstName,
PARSENAME(REPLACE(@name,' ','.'),1) as LastName


so then your statement would be this

select PARSENAME(REPLACE(FullName,' ','.'),2) as FirstName,
PARSENAME(REPLACE(FullName,' ','.'),1) as LastName
From YourTable

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/