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?
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.
There is an IsNumeric() function that you can call in SQL Server. Careful of scientific notation though...
ISNUMERIC() will also think ., e, [tab], $ and € are numeric. classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.htmlPlease 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
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;
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 ($).
