SQL Server - Error converting data type nvarchar to bigint

Question

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

Answer 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.