Oracle SQL Remove all text after chr(32)

Posted on 2003-03-10
Medium Priority
Last Modified: 2006-11-17
Hello all
This is my first question.

Im pulling data out of an oracle database with asp.

Im trying to construct an email address on the fly from payroll data. Unfortunately the forename field also contains middle names, so i want to remove all text after the space (chr(32)).

Im using this code which does the job if the field contains a middlename (ie a space), but leaves the field blank if only the first name is entered.
I need it to return the fistname irrespective of whether it contains a middle name.

select substr(per_forename,1,(instr(per_forename,chr(32),1,1)-1))as email from mytable

"julian gregson" returns "julian"
"julian" returns ""

Hope you can help
Thanks in advance.


Question by:JGregson
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
LVL 43

Accepted Solution

TimCottee earned 200 total points
ID: 8102110
Hi JGregson,

> select substr(per_forename,1,(instr(per_forename,chr(32),1,1)-1))as email from mytable
How about

select substr(per_forename,1,(instr(per_forename + Chr(32),chr(32),1,1)-1))as email from mytable

As this always ensures that there is a space at the end so your statement will always return everything up to the first space.


Brainbench MVP for Visual Basic

Author Comment

ID: 8102295
Had to slightly alter the code to:

select substr(per_forename,1,(instr(concat(per_forename,chr(32)),chr(32),1,1)-1)) as email

Many Thanks

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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