CASE statement using a "tronc"?
My goal is to make a Case statement that says when Bill Credit THEN x when Bill Payment then Y but the field Bill Credit/Payment contain also the reference number in it like Bill Credit #123 so I can't use;
Case {type} when 'Bill Credit' then X ELSE 0 END because there the ref number in it.
Si I'd like something like;
Case when {type} "start with" 'Bill Credit' then X Same thing for Bill Payment.
Thanks,
2 answers

I'm a little confused, because
{type}
would not contain any reference numbers, but it sounds like the function you're looking for isSUBSTR()
Here's an example of usage"
CASE WHEN SUBSTR({field}, 1, 11) = 'Bill Credit' THEN X ELSE 0 END
The first argument is the input string, the second is the starting position, and the third is the length of the substring to return.

This is one option you might use:
SQL> with test (bill) as 2 (select 'Bill Credit #123' from dual union all 3 select 'Bill Credit #566' from dual union all 4 select 'Bill Payment #32' from dual union all 5 select 'Bill Payment' from dual 6 ) 7 select bill, 8 case when regexp_substr(bill, '\w+', 1, 2) = 'Credit' then 'X > credit' 9 when regexp_substr(bill, '\w+', 1, 2) = 'Payment' then 'Y > payment' 10 end result 11 from test; BILL RESULT   Bill Credit #123 X > credit Bill Credit #566 X > credit Bill Payment #32 Y > payment Bill Payment Y > payment SQL>
Depending on what you really have, this code might need to be modified. If you provide test case, it would be easier to assist.