Problem with case expression

In the Portal1 case expression in the script at the bottom I would like
to replace where the result 1 is returned, with the substring function
returned as Portal

{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}

However, I am experiencing errors. I think it is because The substring
function will not return a number as the case expression expects so I
must incorporate cast or convert, but do not know how. Can you help?

SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8)
- (CHARINDEX('tonep', Field1) + 4)) AS portal,
Table.*
FROM TableOn 30 Sep 2005 02:02:28 -0700, chudson007@.hotmail.com wrote:

>In the Portal1 case expression in the script at the bottom I would like
>to replace where the result 1 is returned, with the substring function
>returned as Portal
>{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
>Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
>However, I am experiencing errors. I think it is because The substring
>function will not return a number as the case expression expects so I
>must incorporate cast or convert, but do not know how. Can you help?
(snip)

Hi chudson007,

From your description, I don't understand what you're trying to achieve.
Maybe you could illustrate this some more by providing

- The table structure, posted as CREATE TABLE statements (irrelevant
colunms may be omitted, but please include all constraints)
- Some illustrative rows of sample data, posted as INSERT statements
- The expected output or results

See www.aspfaq.com/5006 for more details and some hints on how to
assemble this info.

Best, Hugo
--

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

As Hugo has requested DDL and sample data is really the only way to know
exactly what is happening.

But, when Field1 is less than 8 characters or does not contain ".txt" or
"tonep" you may get problems.

John

<chudson007@.hotmail.com> wrote in message
news:1128070948.064118.208110@.z14g2000cwz.googlegr oups.com...
> In the Portal1 case expression in the script at the bottom I would like
> to replace where the result 1 is returned, with the substring function
> returned as Portal
> {SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
> However, I am experiencing errors. I think it is because The substring
> function will not return a number as the case expression expects so I
> must incorporate cast or convert, but do not know how. Can you help?
>
>
> SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
> SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8)
> - (CHARINDEX('tonep', Field1) + 4)) AS portal,
> Table.*
> FROM Table