Problem with case expression

[chudson007]

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

[Hugo Kornelis]

On 30 Sep 2005 02:02:28 -0700, ch********@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)

[John Bell]

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

<ch********@hotmail.com> wrote in message
news:11**********************@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