8.0 - String manipulation puzzle

I have a varchar2 field which sometimes contains

     GEORGEBUSH
and other times contains

     GEORGEBUSH#A1234
where the '#' is the delimeter between the "text" and the "code".

I am trying to create a query such that when the field contains

     GEORGEBUSH#A1234
a calculated value of A1234 is returned, but when the field contains

     GEORGEBUSH
the calculated value is null. And - I would like to do this with functions valid in a standard query, without writing a procedure.

Extracting the text is easy:
  substr(table1.field1, 1, (instr(table1.field1, '#')-1)) sw_newname

but the corresponding extraction for the code is harder because the straightforward
  substr(table1.field1, (instr(table1.field1, '#') + 1), length table1.field1)) sw_code

returns the value of the field when there is no # in the data. I feel sure this can be done with some tricky SUBSTR indexing but it is beyond me at the moment. Any suggestions?

Thanks!

sPh Received on Tue Feb 19 2002 - 16:15:27 CST