Fixing the bigint Argument Error in SQL Server's Substring Function

Описание: Learn how to resolve the argument data type error in SQL Server when using the substring function with bigint data types by using casting effectively.
---
This video is based on the question https://stackoverflow.com/q/65275698/ asked by the user 'Ahmed Mohammed Abdel Kader' ( https://stackoverflow.com/u/5738092/ ) and on the answer https://stackoverflow.com/a/65275900/ provided by the user 'Hasan Fathi' ( https://stackoverflow.com/u/3526038/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Argument data type bigint is invalid for argument 1 of substring function

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the SQL Server Error: Argument Data Type bigint is Invalid

When working with SQL Server, you may occasionally encounter errors that can be puzzling and frustrating. One such error is the message stating that the argument data type bigint is invalid for the substring function. This issue often arises when the data type of the column you're working with does not align with the expected input types of the function. In this guide, we will dive into the causes of this error and provide a solution that involves effective casting techniques.

The Problem: Error in Using the substring Function

In your case, you are attempting to manipulate a column named NationalId, which is of type int. The relevant part of your SQL code that is causing the error looks something like this:

[[See Video to Reveal this Text or Code Snippet]]

However, using the SUBSTRING function on NationalId directly as an integer leads to the error, as SUBSTRING expects a varchar data type.

Solution: Properly Casting the Data Type

To fix this issue, you should cast the NationalId to a varchar before applying the SUBSTRING function. This way, you ensure that the function processes the string representation of the number, rather than an invalid bigint type. Here’s how you can do it:

Step-by-Step Code Correction

Follow these changes to your SQL statement:

Cast NationalId to varchar: Before applying any string functions, ensure that NationalId is in the correct format.

Use LEFT and SUBSTRING on the casted value: This allows the string functions to operate on the character representation of the number.

Your corrected SQL code would look like this:

[[See Video to Reveal this Text or Code Snippet]]

Key Notes:

Casting: Using CAST(c.NationalId AS varchar(32)) converts the NationalId into a string format before performing any substring operations.

Data Type Compatibility: Ensure that all string manipulations are performed on a compatible data type to avoid errors.

Conclusion

Casting your NationalId column correctly resolves the bigint argument type error when using the substring function. This technique is essential for preventing data type mismatches in SQL queries. By implementing the changes discussed, you can eliminate errors and successfully extract the desired data from your SQL Server database. Happy querying!