You Asked

Here's the situation.
System is used in several countries. User logs in and can only be at one country at a time.
There's id in the system, which belongs to one country.

There are functions/procedure in the system, which are country specific.
For example the very easy one: get_full_name
- in country A it returns first_name + last_name
- in country B it returns last_name + first_name

And there are others, much more complicated.

So my question is how to implement those country specific functions, so that they can be used in other packages? Without dynamic code and lots of IFs (if country = A, return this, etc.).

and Chris said...

Without dynamic code and lots of IFs (if country = A, return this, etc.).

At a minimum you're going to need an IF/CASE clause per unique format you want to return.

To avoid having to hard-code every country, I'd:

- Create a lookup table of countries
- Have a format code column on this table
- Use this value in a CASE statement/IF

For example:

select name_format_code
into   format_code
from   countries c
where  c.country_code = country_param;

case
  when format_code = 'FIRST_LAST'
  then formatted_name := first_name || ' ' || last_name;
  when format_code = 'LAST_FIRST'
  then formatted_name := last_name || ', ' || first_name;
end;

This means you only need to change the function if you have to support a new format. Adding a new country or changing the format for an existing one is just a matter of running the appropriate insert/update.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

formatting regexp instead of IF .. / code changes

Dieter B�cherl, April 11, 2019 - 7:36 am UTC

Something along the lines of

WITH formats AS
 (SELECT 'EN' code,
         '\1 \2' fmt,
         'first last' coment
    FROM dual
  UNION ALL
  SELECT 'DE' code,
         '\2, \1' fmt,
         'last, first' coment
    FROM dual
  UNION ALL
  SELECT 'FR' code,
         '\2 \1' fmt,
         'last first' coment
    FROM dual),
names AS
 (SELECT 'Johnny Depp' uniform_name_string
    FROM dual d
  UNION ALL
  SELECT 'Jean Gabin' uniform_name_string
    FROM dual d)
SELECT names.*,
       formats.*,
       regexp_replace(names.uniform_name_string,
                      '(\w+)\W*(\w+)',
                      formats.fmt) formatted_name_string
  FROM names
 CROSS JOIN formats;

might work

April 11, 2019 - 10:35 am UTC

That's going to get messy. Fast.

WITH formats AS
 (SELECT 'EN' code,
         '\1 \2' fmt,
         'first last' coment
    FROM dual
  UNION ALL
  SELECT 'DE' code,
         '\2, \1' fmt,
         'last, first' coment
    FROM dual
  UNION ALL
  SELECT 'FR' code,
         '\2 \1' fmt,
         'last first' coment
    FROM dual),
names AS
 (SELECT 'Sacha Baron Cohen' uniform_name_string
    FROM dual d
  UNION ALL
  SELECT 'Catherine Zeta-Jones' uniform_name_string
    FROM dual d)
SELECT names.*,
       formats.*,
       regexp_replace(names.uniform_name_string,
                      '(\w+)\W*(\w+)',
                      formats.fmt) formatted_name_string
  FROM names
 CROSS JOIN formats;

UNIFORM_NAME_STRING    CODE   FMT      COMENT        FORMATTED_NAME_STRING     
Sacha Baron Cohen      EN     \1 \2    first last    Sacha Baron Cohe n        
Sacha Baron Cohen      DE     \2, \1   last, first   Baron, Sacha n, Cohe      
Sacha Baron Cohen      FR     \2 \1    last first    Baron Sacha n Cohe        
Catherine Zeta-Jones   EN     \1 \2    first last    Catherine Zeta-Jone s     
Catherine Zeta-Jones   DE     \2, \1   last, first   Zeta, Catherine-s, Jone   
Catherine Zeta-Jones   FR     \2 \1    last first    Zeta Catherine-s Jone

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

formatting regexp instead of IF .. / code changes

More to Explore

PL/SQL demos

PL/SQL docs

How would you implement this? Returning name format based on country code

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

formatting regexp instead of IF .. / code changes

More to Explore

PL/SQL demos

PL/SQL docs