Is there an equivalent or alternative to the following?
SELECT mix_type || ' (' || mix_num || ')' as description
FROM acid_batch
WHERE mix_num < 10
Does Oracle have something like printf style formatting?
SELECT printf("%s (%s)", mix_type, mix_num) as description,
FROM acid_batch
WHERE mix_num < 10
The closest standard approximation to printf for Oracle I can think of is utl_lms.format_message. However, it won't work in SQL statements, that is, this is ok:
begin
dbms_output.put_line(
utl_lms.format_message('hello %s, the number is %d', 'world', 42)
);
end;
/
but this gives a ORA-00902: invalid datatype error:
select utl_lms.format_message('hello %s, the number is %d', 'world', 42)
from dual
I've made a simple template engine named ora_te (on GitHub) for Oracle SQL / PLSQL. With the help of it your goal can be achieved in the following ways:
Noneffective implementation with multiple parsings of template string:
with acid_batch as (
select rownum as mix_type, rownum + 2 as mix_num
from all_objects
where rownum < 10
)
--
SELECT pk_te.substitute('$1 ($2)', ty_p( mix_type, mix_num ) ) as description
FROM acid_batch
WHERE mix_num < 10;
An effective implementation with one time compilation (parsing):
with acid_batch as (
select rownum as mix_type, rownum + 2 as mix_num
from all_objects
where rownum < 10
),
--
o as (
select ty_te.compile_numbered( '$1 ($2)' ) te from dual
)
SELECT pk_te.substitute( o.te, ty_p( mix_type, mix_num ) ) as description
FROM acid_batch, o
WHERE mix_num < 10;
BTW it also supports named placeholders.
No there are no built-in Oracle functions that apply a formatting string in this fashion. Although it would be easy to write a custom function for this specific example, writing a PL/SQL-based implementation of printf would be challenging.
If you have a frequent need for this, perhaps you could write an Oracle function that wraps a Java call for a richer string handling environment.
Just another idea for you: I've found REPLACE to be useful for this kind of thing, especially when the template is complex:
SELECT REPLACE(REPLACE(
'%mix_type% (%mix_num%)' /*template*/
,'%mix_type%', mix_type)
,'%mix_num%' , mix_num ) as description,
FROM acid_batch
WHERE mix_num < 10
The only downside is you need to add as many REPLACE(
's as there are variables to replace - but at least you only need to have one per variable, regardless of how many times it appears in the template.
(NOTE: There is no particular significance to using "%" as a delimiter, it's just a personal convention of mine - you might choose a different pattern, e.g. <mix_type>
or [mix_type]
)
For this particular instance it looks like overkill, but in some cases it can make things much easier, e.g.:
template := 'bla bla %a% %b% %a%';
output := REPLACE(REPLACE(template
,'%a%', some_complex_expression)
,'%b%', b);
Compare the above with:
output := 'bla bla ' || some_complex_expression || ' ' || b || ' ' || some_complex_expression;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With