Ошибка при преобразовании типа данных nvarchar к real

Presently I am troubleshoot a SQL Server 2008 query that started failing. Here is the query:

SELECT     TOP (100) PERCENT dbo.tblBenchmarkData.FieldDataSetID, dbo.tblBC.BCID, CAST(dbo.tblBenchmarkData.DataValue AS float(8)) AS DataValue, 
                  dbo.tblBC.BCMnemonic, dbo.tblDataType.DataTypeMnemonic
FROM         dbo.tblFieldDataSet RIGHT OUTER JOIN
                  dbo.tblBenchmarkData ON dbo.tblFieldDataSet.FieldDataSetID = dbo.tblBenchmarkData.FieldDataSetID LEFT OUTER JOIN
                  dbo.tblBC LEFT OUTER JOIN
                  dbo.tblDataType ON dbo.tblBC.DataTypeID = dbo.tblDataType.DataTypeID RIGHT OUTER JOIN
                  dbo.tblZEGCode ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID ON dbo.tblBenchmarkData.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID
WHERE     (dbo.tblDataType.DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}') AND (dbo.tblFieldDataSet.OriginalFieldDataSetID IS NULL) AND 
                  (dbo.tblFieldDataSet.Duplicate = 0)
ORDER BY dbo.tblBC.BCMnemonic, DataValue

When I remove the cast and execute the query it returns about 1 400 000 rows, so I take the DataValue results and run a small C# program against this output to verify that all the data is in fact numeric:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   intLineCounter++;
}

The program indicates that there are no data rows that are not numeric, so does anyone have any suggestions as to why I would be receiving this error? TIA.

UPDATE:
Here is the code I wrote to verify that it was checking every line of data:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   else
   {
       Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Number = " + strValue);
   }
   intLineCounter++;
}

This gives results like:

....

Line: 241564, Number = 1843.2
Line: 241565, Number = 18430
Line: 241566, Number = 18430.9
Line: 241567, Number = 18431.6
Line: 241568, Number = 18433.9
Line: 241569, Number = 1844.52

....

UPDATE 2:
Pasted code above from the full original view.

Источник

I need to subtract two nvarchar values and store the result in another column. I understand that first I need to convert nvarchar to numeric values, but I always get this error

Error converting data type nvarchar to real

Just to mention in varchar values are stored only numeric values but I can’t seem to get this conversion right.

marc_s

734k176 gold badges1332 silver badges1460 bronze badges

asked Feb 18, 2020 at 11:29

This looks like a SQL Server error. If so, use try_convert():

select try_convert(real, string_col)

This returns NULL instead of an error if there are conversion issues.

You can find the offending values using:

select string_col
from t
where try_convert(real, string_col) is null and
      string_col is not null;

answered Feb 18, 2020 at 11:32

Gordon LinoffGordon Linoff

1.2m58 gold badges647 silver badges791 bronze badges

use a case statement. It should be something like (t-sql pseudo code inside a SELECT)

case when IsNumeric(column1) AND IsNumeric(column2) 
     then cast(column1 as decimal) - cast(column2 as decimal) 
else NULL end as column3

This is a classical example of why you shouldn’t mix types (nvarchar and decimal in this case), there are all sorts of shananigans — like (‘abc’ — 5) or (NULL — NULL) or (‘five’ — ‘3 ‘). If this works, it might be a good idea to look at the «NULL» results, as this might give you valuable insights as to what kind of garbage is being collected in your system.

answered Feb 18, 2020 at 11:39

LongChalkLongChalk

7838 silver badges13 bronze badges

You said that…

Just to mention in varchar values are stored only numeric values but I
can’t seem to get this conversion right.

If that is absolutely true, then there’s a very high probability that there are 1 or 2 trailing control characters (Tab, Carriage Return, Line Feed, whatever) in the items causing the errors.

Try converting the NVARCHAR values to the MONEY datatype first. It won’t work for leading control characters but it will for trailing control characters. Obvioussly your original original value doesn’t haven more than 4 decimal places, of course.

answered Feb 19, 2020 at 6:08

Jeff ModenJeff Moden

3,2712 gold badges27 silver badges23 bronze badges

Источник

Remove From My Forums

Вопрос
I am trying the execute this query in Query Designer of Report Builder 2.0
SELECT [Mfg Site], Year, FW
FROM dbo.vFurnace_Cockpit_Yield
WHERE dbo.vFurnace_Cockpit_Yield.[Mfg Site] IN (@MfgSite)
AND dbo.vFurnace_Cockpit_Yield.Year IN (@Year)
AND dbo.vFurnace_Cockpit_Yield.FW IN (@FW)

It throws an error: Error converting data type nvarchar to real
The datatypes of [Mfg Site] is varchar(20), Year real, FW real

It works in SQL Server 2008
SELECT [Mfg Site], Year, FW
FROM dbo.vFurnace_Cockpit_Yield
WHERE dbo.vFurnace_Cockpit_Yield.[Mfg Site] IN (‘US1002′,’US1003’)
AND dbo.vFurnace_Cockpit_Yield.Year IN (2009,2010)
AND dbo.vFurnace_Cockpit_Yield.FW IN (43,29,33,7)

Ответы

Hi,

What data type are you choosing for the @Year and @FW when you setup them in the Parameter properties box? It shoule be Integer. In addition, i have a question why you don’t have the year/FW with Integer type in the underlying table?

thanks,
Jerry
- Помечено в качестве ответа
  
  13 июля 2010 г. 8:45

Источник

In this guide, we will walk you through the process of troubleshooting and fixing the Error converting data type NVARCHAR to Numeric issue. This error usually occurs when you attempt to convert an NVARCHAR column to a numeric data type in SQL Server. The error occurs when the NVARCHAR column contains a value that cannot be converted to a numeric data type.

Understanding the Error
Common Causes
Step-by-Step Solution
FAQ
Related Links

Understanding the Error

The Error converting data type NVARCHAR to Numeric issue occurs when SQL Server tries to convert a non-numeric value in an NVARCHAR column to a numeric data type. For instance, when you attempt to update or insert data into a table that contains an NVARCHAR column with a non-numeric value, and a constraint or trigger exists that requires the column to have a numeric value, the error occurs.

SQL Server supports various data types for storing different types of data. NVARCHAR is a variable-length Unicode character data type, while numeric is a data type that can store exact numeric values with a fixed number of decimal places.

Common Causes

Non-numeric characters in the NVARCHAR column
Incorrect data type conversion function
Implicit data type conversion

Step-by-Step Solution

Step 1: Identify the Non-Numeric Values

The first step in fixing the error is identifying the non-numeric values in the NVARCHAR column. You can use the ISNUMERIC function to filter out non-numeric values in the column. Here’s an example query to find non-numeric values in the YourColumnName column of the YourTableName table:

SELECT *
FROM YourTableName
WHERE ISNUMERIC(YourColumnName) = 0

Step 2: Update or Remove Non-Numeric Values

Once you have identified the non-numeric values, you can either update them with numeric values or remove them altogether. To update a specific row, you can use the following query:

UPDATE YourTableName
SET YourColumnName = 'YourNewNumericValue'
WHERE YourPrimaryKeyColumn = 'PrimaryKeyValue'

To remove a specific row, you can use the following query:

DELETE FROM YourTableName
WHERE YourPrimaryKeyColumn = 'PrimaryKeyValue'

Step 3: Use the Correct Data Type Conversion Function

When converting an NVARCHAR column to a numeric data type, make sure you are using the correct data type conversion function. For instance, use the CAST or CONVERT function to explicitly convert the column to a numeric data type:

SELECT CAST(YourColumnName AS NUMERIC(18, 2))
FROM YourTableName

SELECT CONVERT(NUMERIC(18, 2), YourColumnName)
FROM YourTableName

FAQ

What is the difference between NVARCHAR and VARCHAR data types?

NVARCHAR is a Unicode variable-length character data type that can store both Unicode and non-Unicode characters.
VARCHAR is a non-Unicode variable-length character data type that can store only non-Unicode characters.

How can I avoid implicit data type conversion?

To avoid implicit data type conversion, always use the correct data type for your columns when creating or altering tables. Also, use the CAST or CONVERT functions when you need to explicitly convert one data type to another.

What is the difference between CAST and CONVERT functions?

Both CAST and CONVERT functions are used to convert one data type to another. The main difference between the two is the syntax. The CAST function uses the ANSI SQL-92 syntax, while the CONVERT function uses the SQL Server-specific syntax.

Can I convert an NVARCHAR column to an INT data type?

Yes, you can convert an NVARCHAR column to an INT data type using the CAST or CONVERT functions. However, before converting, make sure that the NVARCHAR column contains only integer values, otherwise, an error will occur.

Can I use the TRY_CONVERT function to avoid the error?

Yes, you can use the TRY_CONVERT function to avoid the error. The TRY_CONVERT function returns a NULL value if the conversion fails, instead of raising an error. Here’s an example:

SELECT TRY_CONVERT(NUMERIC(18, 2), YourColumnName)
FROM YourTableName

Data Types (Transact-SQL)
CAST and CONVERT (Transact-SQL)
ISNUMERIC (Transact-SQL)
TRY_CONVERT (Transact-SQL)

Источник

#sql-server

#c# #mysql #sql #sql-обновление

Вопрос:

Код, который у меня есть, приведен ниже, не могли бы вы сообщить мне, в чем может быть проблема, чтобы мы могли снизить цену по идентификатору склада на 7%?

         private void button4_Click(object sender, EventArgs e)
    {
        try
        {
            myConnection = new SqlConnection(frm.cs);
            myCommand = new SqlCommand("update Inventory set Price=@Price where WarehouseCode=6", myConnection);
            myConnection.Open();
            myCommand.Parameters.AddWithValue("@Price", "@Price * 0.7");
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            MessageBox.Show("Update successfully!");
            DisplayData();
            if (myConnection.State == ConnectionState.Open)
            {
                myConnection.Dispose();
            }
        }

Ответ №1:

Поскольку вы не присваиваете никакого значения @Price в коде C #, в то время как ваш Warehouse Id и Discount rate должны быть назначены конечным пользователем, я рекомендую, чтобы ваш код был таким:

    private void button4_Click(object sender, EventArgs e)
   {
        try
        {
            var discountRate = 0.07; //could be Convert.ToDouble(textBox1.Text) or something else
            var warehouseId = 6;    //again, could be Convert.ToInt32(textBox2.Text) or something else
            myConnection = new SqlConnection(frm.cs);
            myCommand = new SqlCommand("update Inventory set Price=Price*(1-@DiscountRate) "  
                                        "where WarehouseCode=@WarehouseId", myConnection);
            myConnection.Open();
            myCommand.Parameters.AddWithValue("@DiscountRate", discountRate);
            myCommand.Parameters.AddWithValue("@WarehouseId", warehouseId);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            MessageBox.Show("Update successfully!");
            DisplayData();
            if (myConnection.State == ConnectionState.Open)
            {
               myConnection.Dispose();
            }
        }
        catch
        {

        }
    }

Я бы также рекомендовал вам еще раз подумать о своем запросе, поскольку он обновит цену всех продуктов с тем же значением, вы можете рассмотреть возможность передачи параметра @ProductId , и ваш запрос будет

 update Inventory set Price=Price*(1-@DiscountRate) 
where WarehouseCode=@WarehouseId and ProductCode=@ProductId

уверен, что это было, например.

1. А что, если складов 2 с двумя разными идентификаторами, например, 6 и 1? Другими словами, цены на все товары на обоих этих складах должны измениться. Вышеуказанное отлично сработало для одного из хранилищ, но второе не сработало даже после добавления второго объявления для WarhouseID1 = 3;

2. Здесь вы можете рассмотреть WarehouseId IN (6,1)

3. Таким образом, ваш параметр должен быть строкой идентификатора, я не рекомендую это делать, поскольку для этого потребуется синтаксический анализ этой строки

4. Вы можете вызвать запрос дважды или столько, сколько вам нужно, с разными идентификаторами

5. Я вижу, спасибо, что это тоже сработало. Однако, когда мы попытались восстановить разницу в 7%, изменив минус на плюс, это не сработало: myCommand = new SqlCommand(«обновить цену набора инвентаря = Цена Цена * @discountRate » «где код склада В (1,3)», MyConnection); MyConnection . Open(); Есть ли причина для этого?

Ответ №2:

Я не думаю, что вам действительно нужен параметр. Если вы хотите снизить цену на 7%, то вам следует выполнить вычисления непосредственно в запросе. Вам также необходимо исправить арифметику:

 update Inventory set Price = Price *  0.93 where WarehouseCode = 6

Или, может быть, вы хотели передать скидку в качестве параметра. Если это так:

 update Inventory set Price = Price * (1 - @Discount) where WarehouseCode=6", myConnection

И вы бы передали значение 0.07 в качестве значения параметра @Discount .

Вопрос

Ответы

Table of Contents

Understanding the Error

Common Causes

Step-by-Step Solution

Step 1: Identify the Non-Numeric Values

Step 2: Update or Remove Non-Numeric Values

Step 3: Use the Correct Data Type Conversion Function

FAQ

What is the difference between NVARCHAR and VARCHAR data types?

How can I avoid implicit data type conversion?

What is the difference between CAST and CONVERT functions?

Can I convert an NVARCHAR column to an INT data type?

Can I use the TRY_CONVERT function to avoid the error?

Вопрос:

Ответ №1:

Комментарии:

Ответ №2:

Комментарии: