set Nonnumeric as they come in to null

Question

I have a paymentHistory field that is an int that tracks how many payments have been made from the account before. I have a source that is varchar for this field and someone enters 1.11 by accident on the front end. I can't change my source but I want to be able to allow only numeric and convert the non-numeric to null values. Can someone please give an example how I would convert values in this PaymentHistory column to only numeric or null. Thanks!

Answer 1

You can reject non-numeric values with a check constraint:

alter table chk_paymenthistory add constraint chk_paymenthistory_num
    check (num not like '%[^0-9]%');

This only allows digits into the column. But you don't want to reject the value, you want to replace it with NULL.

If you are inserting the rows using insert, you can use try_cast()/try_convert():

insert into paymenthistory ( . . . )
    values ( . . ., try_cast(num as int), . . .);

This will convert the value to an integer (and you could use bigint or numeric instead). It will then be saved as a string -- but will be null if it is not a valid number.

That leaves you with a trigger for other situations. But what other situation? What comes to mind is bulk insert, but that doesn't invoke triggers by default. You can turn them on, if you want this functionality.