Re: right-string in SQL? How?

James Peterson (james.peterson_at_teldta.com) wrote:

: 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
: from dual;
 

: 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

Hi,

You can use decode. I attached a script (for 10 chars length):

SQL> get t2
  1 select decode(sign(length('&&1')-10),

  2  		 0,lpad('&&1',13,' '),
  3                  -1, lpad('&&1',13,' '),
  4                   1, '...' || substr('&&1',length('&&1')-10+1, 10))

DECODE(SIGN(L                                                                   
-------------                                                                   
        12345                                                                   

SQL> _at_t2 1234567890

DECODE(SIGN(L                                                                   
-------------                                                                   
   1234567890                                                                   

SQL> _at_t2 1234567890qwer

DECODE(SIGN(L                                                                   
-------------                                                                   
...567890qwer                                                                   

SQL> spool off

Regards, Helen Galuso Received on Thu Jun 27 1996 - 00:00:00 CEST