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

Help parsing a field into 3 separate fields

Hi..
I have field in a table called Supervisors - Need to parse the data in the field into a new table with 3 separate fields. The data looks like this.. thanks!

FROM THIS:

SUPERVISOR

Smith, John   11B
Brown, Jane      188
Green,  Bobby    578


TO THIS:

LAST        FIRST         NUMBER
Smith       John            11B



0
JElster
Asked:
JElster
  • 2
1 Solution
 
rafranciscoCommented:
Here's how to get the last name and the number:

SELECT LEFT(Name, CHARINDEX(',', Name) - 1) AS [Last],
            RIGHT(Name, CHARINDEX(' ', REVERSE(Name)) - 1) AS [Number]
FROM Supervisor

Will see how to retrieve the first name in a while.
0
 
JElsterAuthor Commented:
Some NAME fields do not have an ',' or may be null.. then it throws an error.. how can I only process records with ','
thanks again
0
 
rafranciscoCommented:
SELECT LEFT(Name, NULLIF(CHARINDEX(',', Name) - 1, -1)) AS [Last],
            RIGHT(Name, CHARINDEX(' ', REVERSE(Name)) - 1) AS [Number]
FROM Supervisor
WHERE NAME LIKE '%,%'
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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