Thursday, December 15, 2011

Error converting data type varchar to bigint

Several times you need to convert a varchar field containing alphanumeric value.Like this one :
select [Order_Code]  From [dbo].[Trans_Order]
Using Substring method we can get numeric value.
select SUBSTRING([Order_Code],3,LEN([Order_Code]))  From [dbo].[Trans_Order]
But by using convert or cast method you encountered with a message "Error converting data type varchar to bigint".
select convert(bigint,SUBSTRING([Order_Code],3,LEN([Order_Code])))  From [dbo].[Trans_Order]
So,the actual way to convert any varchar containing only numeric value,is to use ISNUMERIC() method .
In where clause of select statement just check whether string returned by substring method is numeric or not.If its number only then convert or cast method will do their work comfortably.

Now you can use MAX method to get the max value also.

1 comment: