By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

String manipulation challenge in SQL

P: n/a
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?

Oct 26 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Oct 27 '05 #2

P: n/a
Ugly, but functional! Thanks!

Oct 27 '05 #3

P: n/a
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)
Oct 27 '05 #4

P: n/a
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?

Oct 28 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.