-2

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!

an1234
  • 165
  • 1
  • 12
  • 2
    Please show some sample data and the update query used. – Dale K Apr 30 '20 at 21:20
  • 3
    But `1.11` is a numerical value... – Thom A Apr 30 '20 at 21:23
  • @larnu I mean an int. I want it to only be compatible with an int because my target is an int and it failed to insert into my target – an1234 Apr 30 '20 at 21:33
  • So 1.11 should become null not 1? – Dale K Apr 30 '20 at 21:35
  • I would like to do null if possible, but I will take any examples and try them out to see which one works best with the business – an1234 Apr 30 '20 at 21:37
  • Why not add `CHECK CONSTRAINT` on the table? – Thom A Apr 30 '20 at 21:38
  • @Larnu I would rather set it to null if possible – an1234 Apr 30 '20 at 21:40
  • But assuming that `'1.11'` should be `'1'` or `NULL` could be an even worse assumption. Stop people inserting bad data in the first place. – Thom A Apr 30 '20 at 21:41
  • Well test the value using `%` and if there is a remainder set it null using a case expression. – Dale K Apr 30 '20 at 21:41
  • 1
    Considering that a `varchar` that represents a `decimal` can't be converted to an `int`, `TRY_CONVERT` would also be an option. But still, stopping the bad data at the `INSERT` and refusing it is a much better plan. – Thom A Apr 30 '20 at 21:42

1 Answers1

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786