Charindex to not include the delimeter question

Hi, I have this:
select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
from allCats
If I have a column that has no ':' colon in it I get an error about
invalid string. As long as there actually is a delimeter then all is
well. But there isn't always a delimeter in the column value. How can
I make it not give me the error when the column does not have the
delimeter?
Thank you for any help.One option might be to do the following:
SELECT Cat = CASE WHEN myCategory LIKE '%:%' THEN left (mycategory,
CHARINDEX( ':', mycategory) - 1)
ELSE myCategory END
FROM allCats
HTH
Stu|||> How can
> I make it not give me the error when the column does not have the
> delimeter?
Try:
SELECT
CASE CHARINDEX( ':', mycategory)
WHEN 0 THEN mycategory
ELSE LEFT(mycategory, CHARINDEX( ':', mycategory) - 1) END AS Cat
FROM allCats
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146795826.199177.38170@.v46g2000cwv.googlegroups.com...
> Hi, I have this:
> select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
> from allCats
> If I have a column that has no ':' colon in it I get an error about
> invalid string. As long as there actually is a delimeter then all is
> well. But there isn't always a delimeter in the column value. How can
> I make it not give me the error when the column does not have the
> delimeter?
> Thank you for any help.
>|||Another alternative is
select
left(mycategory, charindex(':', mycategory+':') - 1) as Cat
from allCats
Steve Kass
Drew University
needin4mation@.gmail.com wrote:

>Hi, I have this:
>select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
>from allCats
>If I have a column that has no ':' colon in it I get an error about
>invalid string. As long as there actually is a delimeter then all is
>well. But there isn't always a delimeter in the column value. How can
>I make it not give me the error when the column does not have the
>delimeter?
>Thank you for any help.
>
>|||That's very nice; subtle, though.
Stu|||Anyone have any ideas on how to traverse the entire string instead of
just first occurrence?
Thanks for all the answers.
Stu wrote:
> That's very nice; subtle, though.
> Stu|||Using a numbers table as in http://www.aspfaq.com/show.asp?id=2516
you can do this
select substring(mycategory,
Number,
charindex(':',
mycategory + ':',
Number) - Number) as Cat
from allCats
inner join Numbers on Number between 1 and len(mycategory) + 1
and substring(':' + mycategory, Number, 1) = ':'|||Aside from the table-of-numbers method, you'll need to resort to procedural
looping. You can encapsulate the code in a user-defined function to
facilitate reuse. In SQL 2005, you also have CLR languages available which
can do string parsing and manipulation more efficiently than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146868473.352163.33280@.j73g2000cwa.googlegroups.com...
> Anyone have any ideas on how to traverse the entire string instead of
> just first occurrence?
> Thanks for all the answers.
> Stu wrote:
>