SQL: Error Checking to identify entries which are not valid numbers

Question

I would typically load data via bulk insert from a text file into a database. All as nvarchar. I have it all setup as stored procedures which means very fast turn around with minimal rewriting of the scripts. Occasionally however the amount column can cause as issue. For example some extracts we receive contain "-" for 0, others will show negative figure as (10.00). This means when I try to cast amount column as a float I get an error.

What is the best way to catch and identify these random characters in an automated fashion?

catch and identify? what does that mean? What is your desired output? — Tab Alleman, Oct 14 '14 at 12:55
So for example if an entry has "-" instead of 0, then show me that row — Calgar99, Oct 14 '14 at 13:03
True, but I want something that will also catch 1,200 or 1^200 or 1[insert text here]200 — Calgar99, Oct 14 '14 at 13:17
WHERE SomeColumn='-' OR SomeOtherCondition=true OR StillAnotherCondition=true... — Tab Alleman, Oct 14 '14 at 13:19
There must be a cleaner way to capture this rather than inserting a where clause for every possible character or combination of charcaters — Calgar99, Oct 14 '14 at 13:36
Maybe your question is answered here: http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters — Tab Alleman, Oct 14 '14 at 13:41
Is it just one non-numeric character or more? If you have `1200`, will you have cases like '1^20^0` , so multiple possible "splits" ? — Radu Gheorghiu, Oct 14 '14 at 14:09
@TabAlleman Yeah, not really as from what I can see in that link it will only capture numbers that are "contiguous" (not split by characters) — Radu Gheorghiu, Oct 14 '14 at 14:11
@Calgar99 So, in this situation how would you want the number to be extracted? As 1200139.34 ? — Radu Gheorghiu, Oct 14 '14 at 14:15
To be honest I was only thinking as far Identifying and highlighting which rows do not have valid numbers however fixing it also would be a bonus — Calgar99, Oct 14 '14 at 14:16
If you just want to identify, how about the ISNUMERIC function. If it returns 0, set it aside for further evaluation. — Matt, Oct 14 '14 at 14:32

Radu Gheorghiu · Accepted Answer · 2014-10-14T14:42:47.453

2

Well, for identifying I think you can use a regular expression in the form of something like this:

SELECT AmountColumn
FROM yourTable
WHERE PATINDEX('%[^.0-9]%', AmountColumn) > 0

This will catch all of the amounts which have any other characters than numbers, including special characters, letters etc. after they have been inserted in the table.

If you want these values to be treated during the runtime of the stored procedure, I think it would be best to add the code of the SP and some more details.

edited Oct 14 '14 at 14:42

answered Oct 14 '14 at 14:23

Radu Gheorghiu

20,049
16
72
107

Hi Radu, I tried applying your solution, however if an entry is 1.2 then it appears in the list. I then amended it to '%[^0-9].% however it captures $1$.3 but not $1.3 – Calgar99 Oct 14 '14 at 14:33
@Calgar99 My solution will show you all the amounts that have **anything except numbers**, inlcuding dots. I will update my answer to something that will work correctly with `1.3` – Radu Gheorghiu Oct 14 '14 at 14:41
@Calgar99 Please try now. All that you had to do is move the dot from outside of the square paranthesis to the inside, before the 0. Please see updated answer. – Radu Gheorghiu Oct 14 '14 at 14:43

SQL: Error Checking to identify entries which are not valid numbers

1 Answers1