Example 5: Identify, correct, and prevent salary issues

As you can see in the image, we still have a couple of problems with the salary:

We have values that are not actually numbers
We have salary that is negative (that’s really not an idea we want to spread)

Step 1: Identify

Let’s start with the entries that are not actually numbers. We want to find all the rows which contain such non-numbers.
First, we should make very sure what number format we are expecting/allowing.

Caution: This is very Oracle-specific and might work completely different in your database. In our case, we expect the decimal-delimiter to be .

alter session set nls_numeric_characters ='. ';

-- Test our configuration
select to_number('1000.1') from dual;

We have a number of possibilities to identify the problematic rows. One approach is to use regular expressions:

select *
from deathstar_users
where not regexp_like(salary, '^\-?[0-9]+\.?[0-9]*$');

1	Darth Vader	„dark“	15‘000.00	Sith Lord
4	Mace Windu	„light“	–	Prisoner

Or, we could use a PL/SQL function, directly embedded into SQL (again, this very Oracle-specific, other databases might not provide that functionality)

with
  function is_number( i_number varchar2 ) return integer
  as
    l_number number;
  begin
    l_number := to_number(i_number);
    return 1;
  exception when others then
    return 0;
  end;
select *
from deathstar_users
where is_number(salary) = 0
;

1	Darth Vader	„dark“	15‘000.00	Sith Lord
4	Mace Windu	„light“	–	Prisoner

Step 2: Correct (Non-Numbers)

Again, we should manually correct the problems here or at least very carefully watch whatever changes we are making.

Especially with the different decimal separators, it is very risky to use a fully automated, unsupervised approach – we might end up with thousands where we excepted fractions.

update deathstar_users set
  salary = '15000.00'
where salary = '15‘000.00';
update deathstar_users set
  salary = null
where salary = '-';
commit;

Step 3: Prevent (Non-Numbers)

Now that we have valid numbers, we can set our salary to be an actual number field.

Because we cannot change the type of a column that contains values, we need to do this transformation in a couple of small, sequential steps:

-- First: Add a new column of number
alter table t_deathstar_users
  add salary_new number(38,2);

-- Second: Write the values of salary as number into the new column
update t_deathstar_users set
  salary_new = to_number(salary);

-- Third: Remove salary column
alter table t_deathstar_users
  drop column salary;

-- Fourth: Rename the new column
alter table t_deathstar_users
  rename column salary_new to salary;

select name, salary from t_deathstar_users;

Darth Vader	15000.00
Mace Windu
Luke Skywalker	-300.42
Darth Maul	5000.00
Obi-Wan Kenobi	0.00

Step 2: Correct (Negative Numbers)

Now we can correct the negative salaries. It’s a lot easier to do this now that we have actual numbers:

update t_deathstar_users set
  salary = 0
where salary < 0;
commit;

Step 3: Prevent (Negative Numbers)

Finally, we can add a check-constraint and therefore prevent negative numbers to be inserted into the database:

alter table t_deathstar_users
  add check ( salary >= 0 );

select name, salary from deathstar_users;

Darth Vader	15000.00
Mace Windu
Luke Skywalker	0.00
Darth Maul	5000.00
Obi-Wan Kenobi	0.00

It’s now not possible anymore to add negative salaries or salaries that are not valid numbers.

The following insert-statemens both fail:

insert into t_deathstar_users (id, name, salary) 
  values ( 500, 'Leia Organa', -1000);
insert into t_deathstar_users (id, name, salary) 
  values ( 501, 'Chewbacca', 'no money');

The whole example in one script can be found here.

Final Considerations

Of course, from a user experience perspective, we probably want to allow a lot of different entry formats. The important part, however, is that we store the actual numbers in one fixed format that we know and that we can control.

It’s not a problem to convert numbers to any format that we wish.

It’s not a problem to convert specific formats into numbers.

But when we don’t know which format actually applies to which number, we get into a lot of trouble.

Previous: Example 4: Identify, correct, and prevent alignment and roles

Next: Overview