String manipulation challenge in SQL

[wheresjim]

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?

[Steve Jorgensen]

On 26 Oct 2005 16:19:02 -0700, "wheresjim" <wh*******@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

[wheresjim]

Ugly, but functional! Thanks!

[Hugo Kornelis]

On 26 Oct 2005 16:19:02 -0700, wheresjim 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?

Hi wheresjim,

Here's another way. Now that I wrote it, I think I like Steve's version
better - but since YMMV, I'll post it anyway.

DECLARE @a varchar(40)
SET @a = 'segment1-segment2-segment3'
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@a,
CHARINDEX('-', @a) + 1,
LEN(@a))),
CHARINDEX('-', REVERSE(@a)) + 1,
LEN(@a)))
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Steve Kass]

Here's another solution, which should work if you are certain that
the period character . is not part of any of the segments:

SELECT
CASE WHEN value NOT LIKE '%-%-%'
THEN ''
ELSE PARSENAME(REPLACE(value,'-','.'),2) END
FROM @t

Steve Kass
Drew University

wheresjim 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?