Re: right-string in SQL? How?

Hi,

Basically, you need to test the length of the string, and return one of two results. Of course, this is trivial in PL/SQL, but you can do it in SQL also by using SIGN to test the length of the string and doing a DECODE on the results.

define myStr='X234567890'
define amtToKeep=6

col newname format a50

select

decode( sign(length('&&myStr') - &&amtToKeep),
         -1,'&&myStr',
          0,'&&myStr',
             '...'||substr('&&myStr',length('&&myStr') - &&amtToKeep + 1,
                                     length('&&myStr'))
      ) newname

You can experiment by changing the length of myStr and the value of amtToKeep. Essentially this says that if myStr is shorter than or equal to the maximum length, leave it alone, otherwise do your complicated name derivation.

Good luck,

Jim

Bill Parker <bill.parker_at_mns.bt.co.uk> wrote:
>Hi,
>
>I need some help writing what I hope is a trivial bit of SQL.
>
>I need to print out the whole of file_name if it is less than 30 chars.
>wide, else print just the rightmost 30 chars. preceded by ' ...'
>
>But how do I do that?
>
>select if (length(file_name) > 30)
> ' ...'||substr(file_name,length(file_name)-29,30)
>else
> file_name from table;
>
>
>Anyhelp gratefully received. Would appreciate e-mail copies as well...
>this group gets a bit chocker!
>
>Bill
>--
>Boggies are an unattractive but annoying people whose numbers have
>decreased rather precipitously since the bottom fell out of the
>fairy-tale market. Slow and sullen, and yet dull, they prefer to
>live simple lives of pastoral squalor. Bored of the Rings.
 

-- 

=============================

Jim Peterson
TDS Computing Services
Madison, WI USA
james.peterson_at_teldta.com