Error converting data type nvarchar to bigint ошибка

Remove From My Forums

Question
Hi experts,

I execute the next code:

SELECT CAST(Order_Number AS bigint)
FROM Orders
WHERE Order_Number IS NOT NULL
AND ISNUMERIC(Order_Number) = 1
AND Order_Number NOT LIKE ‘%-%’
AND Order_Number NOT LIKE ‘%.%’

And besides having all those checks, it falis with: ‘Error converting data type nvarchar to bigint.’

How can I spot the row that is breaking this? The table has 1,000,000 rows, so I can’t look for it visually.

Thanks in advance!!!

Answers

Using NOT LIKE ‘%[^0-9]%’ should exclude any OrderNumber that has any character other than ‘0’ through ‘9’. Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn’t be trying the conversion
on a record that will not be selected. (If I know that I’m not going to select the record, why would I try to do work on it???)

I’m wondering, do you need to CONVERT(BIGINT, 2147483647)?? Might it be trying to convert the BIGINT into an INT to do the comparison?? (Just a WAG, but I’ve seen stranger things…)

HTH,

Carl
- Proposed as answer by
  
  Thursday, April 21, 2016 6:08 AM
- Marked as answer by
  Sam ZhaMicrosoft contingent staff
  Saturday, April 30, 2016 2:47 AM

When I run following query with SELECT * I get error saying :

[S0005][8114] Error converting data type nvarchar to bigint.

SELECT * FROM (
                SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(id as BIGINT)) AS RowNum
                FROM users
            ) AS users
            WHERE users.RowNum BETWEEN 0 AND 5 ;

When I run this query only with SELECT id , ROW_NUMBER() ... everything works.

My DB looks like this:

This query run well with other table where id column is NVARCHAR

ID column is number only and if i cast it as : CAST(id as NVARCHAR) i get same error.

EDIT:

I Found problem with column ID values

ID 46903836
ID 9100000004

Small ids dont have leading zeros

asked Dec 21, 2016 at 14:05

Lukáš IrsákLukáš Irsák

1,0821 gold badge14 silver badges23 bronze badges

Usually when I get this error it is because there is whitespace on the front or end of the column. Here is how I fix it.

SELECT * FROM (
            SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(LTRIM(RTRIM(id)) as BIGINT)) AS RowNum
            FROM users
        ) AS users
        WHERE users.RowNum BETWEEN 0 AND 5 ;

This will ensure ID is just the number only I am also assuming that there aren’t any alpha characters with the ID.

answered Dec 21, 2016 at 14:10

Wes PalmerWes Palmer

8804 silver badges15 bronze badges

You Don’t need to cast your id column as it is already in bigint datatype

S3S

24.8k5 gold badges25 silver badges44 bronze badges

answered Dec 21, 2016 at 14:13

Your ID field is BIGINT (you have posted your table structure), this don’t cause the error in your question.

But, because is unuseful the CAST you can rewrite your query as follow:

SELECT * FROM (
    SELECT * , ROW_NUMBER() OVER (ORDER BY id) AS RowNum
    FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;

answered Dec 21, 2016 at 14:12

Joe TarasJoe Taras

15.1k7 gold badges40 silver badges55 bronze badges

Источник

Remove From My Forums

Question
Hi: I am getting this data conversion error while I am trying to insert one table into another existing table (appending data). I would really appreciate any assistance on this please.

Thanks

Answers

Hi,

You should list the columns in your SELECT statement in order of the INSERT columns list. But here you haven’t inserted any column. Also you can use a SELECT INFO statement. Therefore a table will be automatically created and you won’t have these problems.
- Marked as answer by
  
  Friday, February 21, 2020 9:44 AM

Источник

SqlZim already gave you a good method to avoid the error in his answer. However, in the question and in comments you seem curious as to why one query throws an error and the other does not. I am able to reproduce your issue:

CREATE TABLE dbo.X_BIGINT_TABLE (ID BIGINT NOT NULL);

INSERT INTO dbo.X_BIGINT_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_NVARCHAR_TABLE (ID_NV NVARCHAR(10) NOT NULL);

INSERT INTO dbo.X_NVARCHAR_TABLE WITH (TABLOCK)
SELECT TOP (999) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
FROM master..spt_values

UNION ALL

SELECT 'ZOLTAN';

This query works fine:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE ISNUMERIC(NV.ID_NV) = 1;

This query throws an error:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Msg 8114, Level 16, State 5, Line 25

Error converting data type nvarchar to bigint.

The SQL Server query optimizer can reorder elements of a query as it sees fit to try to find a query plan with a good enough estimated cost, as long as the changes do not affect the final results of the query. To illustrate the concept lets walk through one possible way the second query can be refactored. To be clear, this is not the actual step-by-step process that the query optimizer goes through for this example. Start with the query:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Push down the predicate:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1
) ZZ;

The derived table is no longer needed so get rid of that:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1

We know that BI.ID = NV.ID_NV so we can apply the filter on Z.ID to NV.ID_NV as well:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500

The join no longer needs to be implemented as INNER JOIN because we are filtering down to a single value for both join columns. We can rewrite as a CROSS JOIN:

SELECT * 
FROM 
(
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    WHERE BI.ID = 500
) 
CROSS JOIN 
(
    SELECT *
    FROM dbo.X_NVARCHAR_TABLE NV
    WHERE ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500
);

If we look at the query plan for the second query we can tell that the end result is very similar to the final transformed query:

Here is the text of the filter predicate for reference:

CONVERT_IMPLICIT(bigint,[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0)=(500) 
AND isnumeric(CONVERT_IMPLICIT(varchar(20),[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0))=(1)

If SQL Server evaluates the CONVERT_IMPLICIT part of the predicate before the isnumeric part then we get an error.

As a general rule, avoid relying on implied order of operations when writing SQL queries. You may have a query that works well today but starts to throw errors if data is added to the table or if a different query plan is chosen. There are, of course, exceptions (kind of). In practice, you will usually see the different parts of a CASE statement to evaluate in the order that you’ve written them, but even then it’s possible to get errors that you weren’t expecting. You can also add a superfluous TOP to parts of your query to encourage a certain order of operations. Consider the following query:

SELECT *
FROM (
    SELECT TOP (9223372036854775807) *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
    ) ZZ 
WHERE ZZ.ID = 500;

You and I know that the TOP will not change the results of the query, However, there is not a guarantee to the optimizer that the derived table won’t return more than 9223372036854775807 rows so it must evaluate the TOP. Technically, in that query we ask for the first 9223372036854775807 rows and then we want to filter out rows with an ID different from 500. Pushing the ID = 500 predicate down to the derived table could change the results so SQL Server will not do that. In this example, the query executes without an error and the filtering is done at the very end:

Источник

Solution 1

Usually when I get this error it is because there is whitespace on the front or end of the column. Here is how I fix it.

SELECT * FROM (
            SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(LTRIM(RTRIM(id)) as BIGINT)) AS RowNum
            FROM users
        ) AS users
        WHERE users.RowNum BETWEEN 0 AND 5 ;

This will ensure ID is just the number only I am also assuming that there aren’t any alpha characters with the ID.

Solution 2

You Don’t need to cast your id column as it is already in bigint datatype

Solution 3

Your ID field is BIGINT (you have posted your table structure), this don’t cause the error in your question.

But, because is unuseful the CAST you can rewrite your query as follow:

SELECT * FROM (
    SELECT * , ROW_NUMBER() OVER (ORDER BY id) AS RowNum
    FROM users
) AS users
WHERE users.RowNum BETWEEN 0 AND 5 ;

Comments

When I run following query with SELECT * I get error saying :

[S0005][8114] Error converting data type nvarchar to bigint.
```
SELECT * FROM (
                SELECT * , ROW_NUMBER() OVER (ORDER BY CAST(id as BIGINT)) AS RowNum
                FROM users
            ) AS users
            WHERE users.RowNum BETWEEN 0 AND 5 ;
```
When I run this query only with SELECT id , ROW_NUMBER() ... everything works.

My DB looks like this:

This query run well with other table where id column is NVARCHAR

ID column is number only and if i cast it as : CAST(id as NVARCHAR) i get same error.

EDIT:

I Found problem with column ID values

ID 46903836
ID 9100000004

Small ids dont have leading zeros
- No need to cast ID as BIGINT, if it’s already a BIGINT. You only need to cast it if it is NVARCHAR. If by any chance you have a letter in your ID field, because it’s NVARCHAR, then there’s no way you can convert to a numeric.
- Well the error message is pretty clear. You have a value in your varchar that is not a valid bigint. This is why you should always use the correct datatype.
- I have a feeling you’re doing something wrong, and what you’re showing us is not the actual cause of the error. I think some details are lost on the way… Are you sure you’re not connected to two databases and querying them differently? Rest assured that your query isn’t wrong, it’s the data in the column or the datatype.
- Unless OP is experimenting with the window functions (always encouraged) seems like a long way to go Select top 5 … Order by ID
- why you cast ID as BIGINT if in your image ID is BIGINT?
While true, it doesn’t explain the error which almost certainly isn’t happening int he posted code snippet
i don’t get that error when i am doing only SELECT * FROM ( SELECT id , ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM users ) AS users WHERE users.RowNum BETWEEN 0 AND 5 ; but i also need other columns like name,email etc…
but if there other col in table for example name,email etc.. you will get that error.
Trailing spaces almost never matter for comparisons (where / having) and especially conversions. Do you have an example where a certain number of leading or trailing spaces caused a conversion error?
If you want other columns, please post your complete request. Because was very strange the error linked to your question query.
I don’t, but I have had trouble casting or converting a NVARCHAR or VARCHAR to a numeric datatype when there are spaces in the string.
In the middle perhaps (which wouldn’t be solved with the trim), but I’ve never seen leading or trailing cause this. was just wondering since your answer alluded to the causation of this problem.
@scsimon: Just try SELECT ISNUMERIC('2') and then try again with a new line after the 2. (rn). The first is true and the second is false and the same would cause an error when converting
@HugoDelsing that’s not a space. That’s a carriage return / line feed. Way different.
Yes it is, I agree. But even though mssql does not remove it with trim, the same method in other languages does. So to avoid confusion I mentioned it. So while I agree that the above example could/should never work, it does provide the necessary information to solve the issue.

Recents

Источник

Question

Answers

Question

Answers

Solution 1

Solution 2

Solution 3

Related videos on Youtube

Comments

Recents

Related