0

How to convert nvarchar to numeric?

I got this error:

Error converting data type nvarchar to numeric.

This is my query

SELECT 
    strIpay88Amt, 
    CONVERT(NUMERIC(18, 2), strIpay88Amt) AS iPay
FROM
    tblCurrTrxMaster
Improve this question
8
  • 2
    And what are the values stored in the strIpay88Amt column? And why - if it's a amount - is this a nvarchar type to begin with?? You should always use the most appropriate datatype - and if you have an Amount, that's certainly NOT nvarchar ! Commented Jan 25, 2020 at 8:52
  • you are correct, but i receive the value in 'nvarchar' thats why i use 'nvarchar'. but i want to convert the amount value to 'numeric' to bring the value to other application. Commented Jan 25, 2020 at 8:55
  • the value in that column 1.00 and NULL Commented Jan 25, 2020 at 8:58
  • 1
    Those values convert to numeric just fine - there must be other values that you're trying to convert that cause this error to be thrown..... the only reason could be that your "locale" uses the comma , as decimal separator, while the strings send you a dot (.). Can you try this: SELECT CAST(N'1.0' AS NUMERIC(18,2)) does that work on your server, or throw an error? Next replace the 1.0 with a 1,0 and try again Commented Jan 25, 2020 at 9:00
  • @marc_c if value is 1.0 it wiil cast but if use( ,) error is happened. Commented Jan 25, 2020 at 9:07

3 Answers 3

Reset to default
2

Assuming that there are string values in strIpay88Amt column. You can use following to convert only numeric values.

select cast(strIpay88Amt as numeric) from tblCurrTrxMaster 
where ISNUMERIC(strIpay88Amt) = 1

And following to fetch string values.

select strIpay88Amt from tblCurrTrxMaster where ISNUMERIC(strIpay88Amt) = 0
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

ISNUMERIC is notoriously bad and returns a lot of "false positives" - I'd much rather use the try_cast function that handles this much more reliably ....
1

There must be some entry in your table that is NOT a valid number and thus your conversion fails - and rightfully so.

So in order to find these entries, you can try this:

SELECT 
    strIpay88Amt, 
    TRY_CONVERT(NUMERIC(18, 2), strIpay88Amt) AS iPay
FROM
    dbo.tblCurrTrxMaster 
WHERE
    TRY_CONVERT(NUMERIC(18, 2), strIpay88Amt) IS NULL

This will use the TRY_CONVERT function to attempt the conversion - and with this WHERE clause, you'll only get back all those entries that could not be converted to a valid NUMERIC.

While NULL will "convert" properly to a numeric, an empty string ('') for instance will already cause an error......

Improve this answer

Comments

0

try cast syntax:

 SELECT strIpay88Amt, CAST(strIpay88Amt AS NUMERIC) AS iPay FROM tblCurrTrxMaster
Improve this answer

6 Comments

its still same error -> Error converting data type nvarchar to numeric.
Are you sure that all of your data in this column is numeric?
No.Actually , i want to convert nvarchar to numeric.
It's happened because you have character in data list of this column.make sure all of data of this column are numeric and then use this syntax.
all data in that column is nvarchar. i want to convert it in numeric.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.