Microsoft SQL Server forum - String manipulation challenge in SQL

Author

String manipulation challenge in SQL

wheresjim

2005-10-27, 9:25 am

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

2005-10-27, 9:25 am

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

Hugo Kornelis

2005-10-27, 5:28 pm

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(RE
VERSE(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

2005-10-28, 3:23 am

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(va
lue,'-','.'),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?
>

Sponsored Links