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
Solved

parsing field in select statement

Posted on 2010-09-19
4
335 Views
Last Modified: 2012-05-10
In SQL Server 2008, I want to run a select statement and parse a text field and user characters up to a colon symbol, which sometimes does not exist.  

The following statement works if all records in that field have a colon, but some don't so it throws an error.

Select substring(myfield,1,charindex(':',title)-1) from mytable

I want to to have a single statement, with a When or if statement I guess, that will return the entire field if there is no colon, otherwise use the substring command.



0
Comment
Question by:dougfosterNYC
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 33711753

hi

firstly u have myfield and also title field.so there could be one field,i guess


Select case when charindex(':',title) != 0 then substring(myfield,1,charindex(':',title)-1) else myfield end as myfield

from mytable
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33711763

ideally there could be only one field.

either myfield or title

Select case when charindex(':',myfield) != 0 then substring(myfield,1,charindex(':',myfield)-1) else myfield end as myfield
0
 

Author Comment

by:dougfosterNYC
ID: 33711805
My bad on the field name. I changed it from title to myfield to keep it general, but forgot to change the first one.

Thanks, that is perfect...
0
 

Author Closing Comment

by:dougfosterNYC
ID: 33711806
fast and right on.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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