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

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"
dbrenneckeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:

select left(yourfield + ';', charindex(';', yourfield + ';' )-1) from yourtable
0
Patrick MatthewsCommented:
Hello dbrennecke,

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

Regards,

Patrick
0
Patrick MatthewsCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

dbrenneckeAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
dbrenneckeAuthor Commented:
unfortunately, I don't own the schema and can't change the declaration... so I need to do this on the fly
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok:
select left(CAST(yourfield as vachar(MAX))+ ';', charindex(';', CAST(yourfield as vachar(MAX))+ ';' )-1) from yourtable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbrenneckeAuthor Commented:
Thanks for hanging with me :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.