• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

SQL Server - Help parsing name field

Hi..
I need help parsing a NAME field into 2 fields

the NAME field data looks like this

"LASTNAME : SMITH   FIRSTNAME:  JOHN"
"LASTNAME : DOE   FIRSTNAME:  Jane"

I need it parsed into   a LASTNAME and FIRSTNAME FIELD

LASTNAME           FIRSTNAME
SMITH                  JOHN
DOE                     Jane
0
JElster
Asked:
JElster
1 Solution
 
lwadwellCommented:
If LASTNAME and FIRSTNAME are the only things in the field and LASTNAME always starts at the beginning:
with cte as (
select 'LASTNAME : SMITH   FIRSTNAME:  JOHN' as name union all
select 'LASTNAME : DOE   FIRSTNAME:  Jane')
select name
     , rtrim(ltrim(substring(name, 11, CHARINDEX( 'FIRSTNAME:' , name )-11))) as lastname
     , rtrim(ltrim(substring(name, CHARINDEX( 'FIRSTNAME:' , name )+10,999))) as firstname
from cte

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now