Solved

SQL Substring Extraction

Posted on 2013-12-04
4
565 Views
Last Modified: 2013-12-04
I need to use SQL to extract first name and last name from a table that stores name as follows:

last_name, first_name mi

ex: 'Doe, Jane L'
      'Smith Jr., Frank C'
      'Brown M.D., Lawrence'

Thanks!
0
Comment
Question by:PattiN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39696397
select last_name&', '&first_name&' '&mi from table

Open in new window

If that doesn't work, replace all of the & with || - I can't remember which is the correct syntax in Sybase.
0
 

Author Comment

by:PattiN
ID: 39696445
I wasn't clear, sorry.  I mean that I need to extract first name as one field and last name as another field from a column in a table that stores the whole name as 'last name, first name mi'.  So, in my example above, I need the following results:

from:  'Doe, Jane L'
         'Smith Jr., Frank C'
         'Brown M.D., Lawrence'

I need two columns in my SQL result set:
First Name             Last Name
Jane                         Doe
Frank                       Smith Jr.
Lawrence                Brown M.D.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 39696807
So what you basically want to do is take out the string, spit it by ',' to separate last name from first/middle and then split first/middle by a space to separate them.

I'm not familiar with how Sybase works with SQL, but this article on splitting strings should contain something that will work for you
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Look especially at the one called "Jeff Moden's splitter." I think that will work for you.

Note that all the comments and discussion on performance won't really apply to you since your strings are relatively short (nowhere near 8000 characters).
0
 

Author Closing Comment

by:PattiN
ID: 39696825
That's great, thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to get time difference in minutes and seconds only between 2 dates 2 63
Math Equation 23 148
some keys in my laptop is not working. any suggestion 6 61
Data Analysis 7 59
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question