Oracle 'printf' equivalent

Question

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

Answer 1

You can resolve it in the select.

SELECT mix_type || '(' ||  mix_num || ')' as description,
FROM acid_batch
WHERE mix_num < 10

you should also take a look at the functions

to_char

to_date

to_number

as they give your a finer granularity on how you want the things represented.

Answer 2

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.

Answer 3

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;