Solved

parsing field in select statement

Posted on 2010-09-19
4
340 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
[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
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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