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

Select partial string

I want to return only part of the string up to the semicolon. In the fields below I want only "Abbott" and "Marshall" returned.

select ename from emp;

Ename
Abbott; Jason Clark George
Marshall; Edward Henry
0
hdcowboyaz
Asked:
hdcowboyaz
  • 6
  • 2
1 Solution
 
njp353Commented:
SELECT SUBSTRING_INDEX(ENAME, ';', 1) AS LNAME FROM EMP;
0
 
hdcowboyazAuthor Commented:
Perfect

How would I return the First name after the semicolon

 Jason
Edward
0
 
Akilandeshwari NCommented:
Try this.
SELECT TRIM(LEADING ';' FROM 'Abbott; Jason Clark George');

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
njp353Commented:
SELECT SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(ENAME, ';', 2)),' ',1) AS FNAME FROM EMP;
0
 
hdcowboyazAuthor Commented:
That returned the Last name followed by the semicolon  
SELECT SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(ENAME, ';', 2)),' ',1) AS EFIRST FROM emp;
Abbott;

This returned the whole name
SELECT SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(ENAME, ';', 2)),' ',3) AS EFIRST FROM emp;
Abbott; Jason Clark George
0
 
hdcowboyazAuthor Commented:
I got it...thanks...

SELECT SUBSTRING_INDEX(ENAME, ';', -1) AS EFIRST FROM emp;
0
 
hdcowboyazAuthor Commented:
Actually it returns everything after the semicolon, instead of the first name only/\.

Ename
Abbott; Jason Clark George
Marshall; Edward Henry

Jason Clark George
Edward Henry
0
 
hdcowboyazAuthor Commented:
This return the last name in the string
SELECT SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(ENAME, ';', 2)),' ',-1) AS EFIRST FROM emp;

George
Henry
0
 
hdcowboyazAuthor Commented:
Got it...

SELECT SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(ENAME, ';', -1)),' ',1) AS EFIRST FROM emp;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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