최고의 답변자
Error converting into float even after checking Isnumeric() function in sql server

-
Hi,
Before I am converting the value into float, I am using the IsNumeric() function to filter non numeric values. But Some value like '- 0.1' passing as numeric and getting error while converting into float. Is there any other way available to achieve this?
Thanks
K Arivazhagan
질문
답변
-
ISNUMERIC returns 1 for many other characters like .,$ etc so its not completely reliable for checking for numeric data alone
much better would be to use a function like this
https://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page- 답변으로 제안됨 Will_KongMicrosoft contingent staff, Moderator 2018년 7월 13일 금요일 오전 8:12
- 답변으로 표시됨 Arivazhagan K 2018년 7월 18일 수요일 오전 5:54
모든 응답
-
ISNUMERIC returns 1 for many other characters like .,$ etc so its not completely reliable for checking for numeric data alone
much better would be to use a function like this
https://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page- 답변으로 제안됨 Will_KongMicrosoft contingent staff, Moderator 2018년 7월 13일 금요일 오전 8:12
- 답변으로 표시됨 Arivazhagan K 2018년 7월 18일 수요일 오전 5:54
-
Use try_convert or try_parse function to avoid errors. You will get null value for invalid values but no error.
- 답변으로 제안됨 Will_KongMicrosoft contingent staff, Moderator 2018년 7월 13일 금요일 오전 8:12
-
Use try_convert or try_parse function to avoid errors. You will get null value for invalid values but no error.
Please notice that function TRY_CAST,TRY_CONVERT,etc could only be performed on the SQL Server 2012 or above.
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. -
In general to check for numeric values, you can use checking like below:
declare @MyString varchar(30) = '-0.1'
SELECT CASE WHEN @MyString NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END
OR
SELECT CASE WHEN @MyString NOT LIKE '%[^0-9,^.]%' THEN 1 ELSE 0 END
You can customize the condition to suit your requirement
-
I should to convert '-0.1' and not '- 0.1', because '-0.1' is numeric value.
yes
replace the space characters in between
CAST(REPLACE(ColumnName,' ','') AS decimal(25,2))
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page