Case Expression

26 Aug 2005 1:52 AM

Adam

Hi all,

I am trying to execute a query that shortens selected string values beyond a
certrain point (40 characters), thus provide a summary.
If the string is less than 40 characters the whole string is returned.

My attempt so far is:

SELECT
id,
Case task
    WHEN Len(task) > 40 Then SUBSTRING(task,1,40) + '...',
    Else
      task
   End,
   hazard,
FROM
items

This is providing me with a syntax error concerning the '>' sign.
Also not sure if i can use Len here.

Any help to get this working is appreciated.

Adam

26 Aug 2005 1:58 AM

Tom Moreau

Try:

SELECT
id,
Case
    WHEN Len(task) > 40 Then SUBSTRING(task,1,40) + '...',
    Else
      task
   End,
   hazard,
FROM
items

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Adam" <a***@pertrain.com.au> wrote in message
news:OU5bDEeqFHA.2076@TK2MSFTNGP14.phx.gbl...
Hi all,

I am trying to execute a query that shortens selected string values beyond a
certrain point (40 characters), thus provide a summary.
If the string is less than 40 characters the whole string is returned.

My attempt so far is:

SELECT
id,
Case task
    WHEN Len(task) > 40 Then SUBSTRING(task,1,40) + '...',
    Else
      task
   End,
   hazard,
FROM
items

This is providing me with a syntax error concerning the '>' sign.
Also not sure if i can use Len here.

Any help to get this working is appreciated.

Adam

26 Aug 2005 3:20 PM

Jeremy Williams

Actually, I do not think you need the CASE expression at all.

If you take the first 40 characters of a value that is less than 40
characters long, you will just get back the original value. So you should
just need to do this (untested):

SELECT
    id,
    SUBSTRING(task,1,40) AS ShortTask,
    hazard
FROM
    Items

Show quote

"Adam" <a***@pertrain.com.au> wrote in message
news:OU5bDEeqFHA.2076@TK2MSFTNGP14.phx.gbl...
> Hi all,
>
> I am trying to execute a query that shortens selected string values beyond
a
> certrain point (40 characters), thus provide a summary.
> If the string is less than 40 characters the whole string is returned.
>
> My attempt so far is:
>
> SELECT
>   id,
>   Case task
>     WHEN Len(task) > 40 Then SUBSTRING(task,1,40) + '...',
>     Else
>       task
>    End,
>    hazard,
> FROM
> items
>
> This is providing me with a syntax error concerning the '>' sign.
> Also not sure if i can use Len here.
>
> Any help to get this working is appreciated.
>
> Adam
>
>

26 Aug 2005 3:36 PM

Jeremy Williams

Sorry, I missed that you were appending the ellipses when the length was
greater than 40. However, that means that the length will actually be 43
characters, not 40 - but I am guessing that is not a big problem.

Show quote

"Jeremy Williams" <jeremydw***@netscape.net> wrote in message
news:eq4jwGlqFHA.3060@TK2MSFTNGP09.phx.gbl...
> Actually, I do not think you need the CASE expression at all.
>
> If you take the first 40 characters of a value that is less than 40
> characters long, you will just get back the original value. So you should
> just need to do this (untested):
>
> SELECT
>     id,
>     SUBSTRING(task,1,40) AS ShortTask,
>     hazard
> FROM
>     Items
>
> "Adam" <a***@pertrain.com.au> wrote in message
> news:OU5bDEeqFHA.2076@TK2MSFTNGP14.phx.gbl...
> > Hi all,
> >
> > I am trying to execute a query that shortens selected string values
beyond
> a
> > certrain point (40 characters), thus provide a summary.
> > If the string is less than 40 characters the whole string is returned.
> >
> > My attempt so far is:
> >
> > SELECT
> >   id,
> >   Case task
> >     WHEN Len(task) > 40 Then SUBSTRING(task,1,40) + '...',
> >     Else
> >       task
> >    End,
> >    hazard,
> > FROM
> > items
> >
> > This is providing me with a syntax error concerning the '>' sign.
> > Also not sure if i can use Len here.
> >
> > Any help to get this working is appreciated.
> >
> > Adam
> >
> >
>
>