Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - How do I strip everything out of a string after a specific character (e.g. everything after a ";")?

Posted on 2009-07-13
8
Medium Priority
?
170 Views
Last Modified: 2012-05-07
In SQL, how do I strip everything out of a string after a specific character (e.g. everything after a ";")?

Original: "I want this information; I do not want this information"
Desired result: "I want this information"
0
Comment
Question by:dbrennecke
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24844263

select left(yourfield + ';', charindex(';', yourfield + ';' )-1) from yourtable
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24844269
Hello dbrennecke,

SELECT CASE
      WHEN CHARINDEX(';', SomeColumn) > 0 THEN LEFT(SomeColumn, CHARINDEX(';', SomeColumn) - 1)
      ELSE SomeColumn END AS SomeColumn
FROM SomeTable

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24844275
dbrennecke,

angelIII's method is to be preferred; his method of concatenating a semicolon automatically handles the
contingency I was using the CASE expression to handle.

Regards,

Patrick
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:dbrennecke
ID: 24844487
Angellll,
Myfield is of type text, resulting in the following error:
"The data types text and varchar are incompatible in the add operator."
My lame attempts to correct this have fallen short :(
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24844504
sql 2005 => change the TEXT data type of your field(s) into VARCHAR(MAX).
solves all those kind of archaic problem with TEXT data type :)
0
 

Author Comment

by:dbrennecke
ID: 24844520
unfortunately, I don't own the schema and can't change the declaration... so I need to do this on the fly
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24844541
ok:
select left(CAST(yourfield as vachar(MAX))+ ';', charindex(';', CAST(yourfield as vachar(MAX))+ ';' )-1) from yourtable

Open in new window

0
 

Author Closing Comment

by:dbrennecke
ID: 31603038
Thanks for hanging with me :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 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