Re: right-string in SQL? How?

Bill Parker wrote:
[snip]
> 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,

You want something like:

select decode(sign(length(file_name) - 30), -- thing to test

              0,   -- 'if' part - return value from 'sign' - might actually 
                   -- need a 1 here; I can't remember which way round it is
              file_name,  -- 'then'
              '  ...'||substr(file_name,length(file_name)-29,30)  -- 'else'

Hope this helps,

MPP

-- 

Martin Purbrook      (MP) +44 1473 605208
martin_at_kbss.bt.co.uk (FAX)+44 1473 622451