Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)

When I am executing the below query with SELECT * I am getting the following error :

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

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

My database looks like this:

Image of database

This query runs well with the other table where the id column is NVARCHAR

The ID column is number only and if I cast it as CAST I get the same error.

1 Answer

0 votes
by (12.7k points)
edited by

Normally when I am getting this error, it is because there is whitespace on the front or end of the column. Here is how to 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 make sure that the ID is just the number only. I am also thinking that there aren't any alpha characters with the ID also check if small ids don't have leading zeros.

Looking for SQL TutorialCome and join the SQL server training course and certification to gain more knowledge on SQL.

Related questions

0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...