Posted by Steve Jorgensen on 10/01/09 11:30

On 26 Oct 2005 16:19:02 -0700, "wheresjim" <wheresjim@gmail.com> wrote:

>I have a sql query in which I need to isolate part of the columm value
>and return only that isolated portion. I can only do this within the
>select statement, and cannot add a function or anything like that. I
>would also like to keep this query within sql (I don't want to do this
>in my programming environment)
>
>The string value would normally look like "segment1-segment2-segment3".
> I need to isolate segment2, but I have to be able to account for
>situations in which either one or both dashes are missing (in which
>case returning "" or the whole string is OK. The best I have been able
>to do reliably is to get "segment2-segment3".
>
>Anybody want to take a stab?

Well - it's pretty damn ugly, but the best I can figure given your
restrictions is...

SELECT CASE
WHEN value LIKE '%-%-%'
THEN SUBSTRING(value,
CHARINDEX('-',value)+1,
CHARINDEX('-',value,
CHARINDEX('-',value)+1) -
CHARINDEX('-',value) -
1)
ELSE 'aa-bb-cc'
END

[Back to original message]