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

x
?
Solved

parsing field in select statement

Posted on 2010-09-19
4
Medium Priority
?
341 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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore 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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

671 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