Posted by John Bell on 10/01/05 14:18

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.googlegroups.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
>

• Next in forum: Re: Maintaining a chain with multiple inserts
• Prev in forum: Re: Unit Testing
• Thread view: Re: Problem with case expression

[Reply to this message]