Removing data in a column after a hyphen

15 pts.
Tags:
SQL Database
SQL statements
We imported a text file and one of the columns has first and last names in it. However, there is sometimes a hyphen with a title after the name. We need to create a SQL statement that will look for the hyphen and remove it and anything following it. Example: Column 1 Column 2 02/18/09 John Doe 07/01/08 Donna Moore - Mgr 09/12/08 Bob Alder - CEO 04/20/09 Jim Summers We want just the first and last names in Column 2, without the hyphen and person's title. So the SQL statement would need to remove the hyphen (when it does occur) and anything following it.

Answer Wiki

Thanks. We'll let you know when a new response is added.

In SQL Server, you could use the CHARINDEX function.

Something like this

<pre>SELECT CASE CHARINDEX(‘-’,names)
WHEN 0 THEN names
ELSE SUBSTRING(names,1,(CHARINDEX(‘-’,names+’-’)-2))
END
FROM your_table</pre>

If the space is not always present between the last name and the hyphen, you coud modify the statement to something like this:

<pre>SELECT CASE CHARINDEX(‘-’,names)
WHEN 0 THEN names
ELSE RTRIM(SUBSTRING(names,1,CHARINDEX(‘-’,names+’-’)-1))
END
FROM your_table</pre>

If Oracle, you could use the INSTR function.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sunitalynn
    Wow...thanks very much!! This sounds like what we need.
    15 pointsBadges:
    report
  • carlosdl
    Notice that the original answer was modified, because it would fail when the hyphen did not appear. When I realized about the error, I thought something like this would work: SELECT CASE CHARINDEX('-',names) WHEN 0 THEN names ELSE RTRIM(SUBSTRING(names,1,CHARINDEX('-',names)-1)) END FROM your_table But it gave me an error when the hyphen did not appeared too, because Sql server evaluates this part: SUBSTRING(names,1,CHARINDEX('-',names)-1) even when CHARINDEX returned 0, and generates an error because it would result in SUBSTRING(names,1,-1) That's why I added the extra '-', to make sure it appears at least once. RTRIM(SUBSTRING(names,1,CHARINDEX('-',names+'-')-1)) Honestly, I don't like this solution very much, so if someone else can suggest another way to do it, please, go ahead.
    69,365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following