We help IT Professionals succeed at work.

SQL Server - Help parsing name field

JElster
JElster asked
on
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
Comment
Watch Question

Project Architect
CERTIFIED EXPERT
Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.