Insert into table only if numeric in SQL

Question

I have an insert query like this

INSERT INTO tbl_LandRigs (Company,Rig,[RigType] ,[DrawWorks],[TopDrive] ) VALUES 
(@Company,@Rig,@RigType ,@DrawWorks,@TopDrive )

Now what i need is ,I want to insert data to column TopDrive only if the data is numeric.Else I want to insert "0" to that column.Is it possible in SQL?

What data type is the TopDrive column? Do you want to accept only integers or any form of number? — Tim Lehner, Aug 27 '13 at 12:39
Instead you can validate in front end itself to accept only numbers.. — Sasidharan, Aug 27 '13 at 12:58

score 2 · Accepted Answer · edited May 23 '17 at 11:49

INSERT INTO tbl_LandRigs ([Company], [Rig], [RigType], [DrawWorks], [TopDrive])
VALUES (@Company, @Rig, @RigType, @DrawWorks, CASE WHEN ISNUMERIC(@TopDrive) = 1 THEN @TopDrive ELSE 0 END)

Here we are putting both CASE and ISNUMERIC to good use.

Also, it usually makes it easier for other developers (or yourself at a later date) to be consistent with your usage of brackets and white space, even in SQL code.

As mentioned by others, isnumeric may give you some false positives. If possible, it's often best to sanitize the input before it gets to your database, perhaps by not using untyped data in this case. Only you know your inputs, but if you anticipate a problem, there are some options.

score 1 · Answer 2 · answered Aug 27 '13 at 12:27

1

There is an IsNumeric() function that you can call in SQL Server. Careful of scientific notation though...

answered Aug 27 '13 at 12:27

ganders

7,285
17
66
114

Can you elaborate what i need to care with scientific notation? – None Aug 27 '13 at 12:28
2

If you have something like "1.23451e+04" or something like that, IsNumeric() will think that's a number... – ganders Aug 27 '13 at 12:31
2

`ISNUMERIC()` will also think `.`, `e`, `[tab]`, `$` and `€` are numeric. http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html – Aaron Bertrand Aug 27 '13 at 12:44

score 1 · Answer 3 · edited Aug 27 '13 at 12:32

1

Please see this:

IF Isnumeric(@TopDrive) = 1 
  BEGIN 
      INSERT INTO TBL_LANDRIGS 
                  (COMPANY, 
                   RIG, 
                   [RIGTYPE], 
                   [DRAWWORKS], 
                   [TOPDRIVE]) 
      VALUES      (@Company, 
                   @Rig, 
                   @RigType, 
                   @DrawWorks, 
                   @TopDrive ) 
  END 
ELSE 
  BEGIN 
      INSERT INTO TBL_LANDRIGS 
                  (COMPANY, 
                   RIG, 
                   [RIGTYPE], 
                   [DRAWWORKS], 
                   [TOPDRIVE]) 
      VALUES      (@Company, 
                   @Rig, 
                   @RigType, 
                   @DrawWorks, 
                   0 ) 
  END

Update if of any help

edited Aug 27 '13 at 12:32

rink.attendant.6

44,500
61
101
156

answered Aug 27 '13 at 12:28

Ashutosh Arya

1,138
2
8
14

Is there a benefit to offset maintaining two insert statements? – Tim Lehner Aug 27 '13 at 12:42
@TimLehner: No absolutely not, If you ask me i would choose your answer :-D ! – Ashutosh Arya Aug 27 '13 at 12:46

score 1 · Answer 4 · edited May 23 '17 at 12:29

Before inserting into sql you check whether it number or not check this link

bool status;
NSScanner *scanner;
NSString *testString;
double result;

scanner = [NSScanner scannerWithString:testString];
status = [scanner scanDouble:&result];
status = status && scanner.scanLocation == string.length;

If status == YES then the string is fully numeric.

Or as @Dave points out from this SO answer:

NSNumberFormatter *formatter = [[NSNumberFormatter alloc] init];
[formatter setNumberStyle:NSNumberFormatterDecimalStyle];
number = [formatter numberFromString:string];
status = number != nil;

Kaf · Answer 5 · 2013-08-27T12:43:03.103

If you are checking only for digits (no decimals) use like '%[^0-9]%' as below;

Insert into tbl_LandRigs (Company, Rig, [RigType], [DrawWorks], [TopDrive] ) 
Values (@Company, @Rig, @RigType, @DrawWorks,
        Case when @TopDrive like '%[^0-9]%' then 0 else @TopDrive end )

Be careful with ISNUMERIC() as it returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

Insert into table only if numeric in SQL

5 Answers5