parsing field in select statement

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.



dougfosterNYCAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:

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
 
Bhavesh ShahLead AnalysistCommented:

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
 
dougfosterNYCAuthor Commented:
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
 
dougfosterNYCAuthor Commented:
fast and right on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.