Go Premium for a chance to win a PS4. Enter to Win

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
?
173 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

773 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