0

Good Day, I am trying to insert records from csv file into my database table. Problem is in inserting alphanumeric values. My column datatype is set to NUMERIC(19,0), in this column I am expecting some numeric values to be inserted from. For some specific reasons I am getting alphanumeric values in my csv file. For example: I am getting value: GBS1182000945008. My goal here is to remove those three characters and cast the remaining string as Numeric and get it inserted inside my table.

So far I have tried:

CAST((select substring(?,4,30)) AS NUMERIC)

But, I am still getting that annoying error, I cannot just ignore those values by using TRY_CONVERT as I do need those records in my database. What am I missing here?

Edit: I have tested this code separately and it is working as expected, only problem is in using it while inserting values. What I have done is that, I checked whether the given parameter is numeric or not, if it is I am just inserting the param if not then I am converting that param into numeric.

So here is my whole scenario:

If (SELECT ISNUMERIC(?)) = 1 {
     // Just insert the parameter as:
     Insert INTO table (NUMERIC_FIELD) VALUE(?)
}
ELSE {
  Insert INTO table (NUMERIC_FIELD) VALUE(CAST((select substring(?,4,30)) AS NUMERIC))
}

Here ? represents the value from CSV.

Anus Kaleem
  • 544
  • 4
  • 16
  • 1
    [Your code is working for me](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f449fb8196685d6af4c745e9f44178c7), and I can't reproduce the problem. – Tim Biegeleisen Jan 03 '19 at 10:45
  • 1
    That one example you have given us won't generate an error. `SELECT CONVERT(numeric,SUBSTRING('GBS1182000945008',4,30))` returns the value `1182000945008`. I therefore suspect you have other values that don't convert, but what those values are we have no idea; we can't see your data. Also, I suggest declaring your precision, scale and length when specifying a datatype. In this case, your `numeric` needs a precision and scale. Not declaring them can end up leading to unexpected behaviour. – Thom A Jan 03 '19 at 10:45
  • I too have tested that on sql server, and it is working. But when I try to insert values using this, I am getting error. Moreover, I have opened my CSV file on Excel also to see if there is any other value creating problems. But, to my surprise there is no other such values that can be problematic in this regard except the one mentioned. – Anus Kaleem Jan 03 '19 at 10:51
  • @AnusKaleem this work for inserting in my case. I have test on mock script. – Sanpas Jan 03 '19 at 10:54
  • @AnusKaleem try to reduce your csv file and identify and show up the line was throwing error. – Sanpas Jan 03 '19 at 10:56
  • @AnusKaleem what is the *actual* code you use? You can't use `CAST((select substring(?,4,30)) AS NUMERIC)` as-is. In ADO.NET or OLEDB it's common to use named parameters, not `?`. How did you use that expression, what does the *entire* query look like and how was it called? – Panagiotis Kanavos Jan 03 '19 at 10:56
  • @PanagiotisKanavos let me update my question – Anus Kaleem Jan 03 '19 at 10:58
  • 2
    Bear in mind that `ISNUMERIC` answers a question nobody has ever seriously *wanted* answered - "can this string be converted to *any* of the numeric data types? If so, I don't care *which ones*". If you're not trying to ask that question, don't use `ISNUMERIC`. – Damien_The_Unbeliever Jan 03 '19 at 11:09
  • @Damien_The_Unbeliever, so what should I use as the base check? Will regex do a trick? – Anus Kaleem Jan 03 '19 at 11:21
  • Go down this rabbit hole, should see the examples you're after. https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters – TEEKAY Jan 03 '19 at 12:53

1 Answers1

0

Try AS NUMERIC(19,0) instead of AS NUMERIC

Also, please note you can have 30 digits in the extracted substring (it will not fit your 19 digis of the column datatype.

Varty
  • 353
  • 1
  • 4