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

x
?
Solved

parsing field in select statement

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

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 this article I will describe the Detach & Attach 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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

879 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