Re: 8.0 - String manipulation puzzle

Look at DECODE and CASE WHEN.

If in 8i you will need to run CASE WHEN either from SQL*Plus or wrap it up as native dynamic SQL.

Daniel Morgan

sPh wrote:

> 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 - 18:12:20 CST