In-depth Analysis and Solutions for Concatenating Numbers and Strings to Format Numbers in T-SQL

Problem Background and Error Analysis

In T-SQL development, concatenating numbers and strings is a common requirement, but improper handling of data type conversion can easily cause runtime errors. This article analyzes the root causes of type conversion errors based on a real user function case and provides multiple effective solutions.

Diagnosis of Original Function Issues

The user's provided function attempts to return weight or dimension information based on input parameters but encounters a "Conversion failed when converting the varchar value 'x' to data type int" error during execution. The fundamental cause of this error lies in SQL Server's type inference mechanism.

-- Problematic code example
SET @ActualWeightDIMS = @Actual_Dims_Lenght + 'x' + @Actual_Dims_Width + 'x' + @Actual_Dims_Height

When SQL Server encounters concatenation operations between integers and strings, it attempts to convert the string to an integer for addition operations, rather than converting the integer to a string for concatenation. This implicit type conversion causes runtime errors.

Solution One: Using CAST Function for Explicit Conversion

The most reliable solution is to use the CAST function to explicitly convert integer parameters to string types, ensuring correct execution of concatenation operations.

-- Corrected code
SET @ActualWeightDIMS = 
    CAST(@Actual_Dims_Lenght AS VARCHAR(16)) + 'x' +
    CAST(@Actual_Dims_Width AS VARCHAR(16)) + 'x' +
    CAST(@Actual_Dims_Height AS VARCHAR(16))

This method is compatible with all versions of SQL Server. The VARCHAR(16) length selection is based on the maximum representation range of integer types, ensuring sufficient storage space.

Solution Two: Using CONCAT Function for Simplified Operations

For SQL Server 2012 and later versions, the CONCAT function can be used to automatically handle type conversion, simplifying code writing.

-- Optimized version using CONCAT function
SET @ActualWeightDIMS = CONCAT(@Actual_Dims_Lenght, 'x', @Actual_Dims_Width, 'x', @Actual_Dims_Height)

The CONCAT function automatically converts all parameters to string types, eliminating the need for explicit type conversion and making the code more concise and readable.

Complete Optimized Function Implementation

Based on the above analysis, we can refactor the original function to provide a more robust and maintainable implementation.

ALTER FUNCTION [dbo].[ActualWeightDIMS]
(
    @ActualWeight INT,
    @Actual_Dims_Length INT,
    @Actual_Dims_Width INT,
    @Actual_Dims_Height INT
)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @Result VARCHAR(50)
    
    -- Return weight information first
    IF @ActualWeight IS NOT NULL
        SET @Result = CAST(@ActualWeight AS VARCHAR(50))
    -- Return dimension information second
    ELSE IF @Actual_Dims_Length IS NOT NULL 
        AND @Actual_Dims_Width IS NOT NULL 
        AND @Actual_Dims_Height IS NOT NULL
        SET @Result = CONCAT(@Actual_Dims_Length, 'x', @Actual_Dims_Width, 'x', @Actual_Dims_Height)
    -- Return NULL by default
    ELSE
        SET @Result = NULL
    
    RETURN @Result
END

Memory Handling Mechanism for String Concatenation

An important concept mentioned in the reference article is SQL Server's memory management mechanism when handling long string concatenation. When using inline assignment syntax, unexpected truncation may occur if the concatenated string length exceeds 4000 characters.

The reason for this phenomenon is that SQL Server evaluates the type of string expressions before assignment. If all components are string literals, SQL Server may infer it as NVARCHAR(4000) instead of NVARCHAR(MAX). To avoid this situation, explicit type conversion is recommended for long strings:

-- Correct way to handle long strings
DECLARE @LongString NVARCHAR(MAX) = CAST(N'Long string content' AS NVARCHAR(MAX)) + additional content

Best Practice Recommendations

Based on the analysis in this article, we summarize the following best practices for T-SQL string handling:

1. Always perform explicit type conversion: In mixed-type operations, prioritize using CAST or CONVERT functions for explicit conversion
2. Choose appropriate string length: Select suitable VARCHAR length based on actual requirements to avoid unnecessary memory waste
3. Utilize CONCAT function: In SQL Server 2012+ environments, prioritize using CONCAT function to simplify code
4. Handle NULL values: Pay attention to NULL value handling in string concatenation, using ISNULL or COALESCE functions
5. Performance considerations: For large-scale string concatenation operations, consider using STRING_AGG function (SQL Server 2017+)

Application Scenario Extensions

The techniques discussed in this article are not only applicable to dimension formatting but can also be extended to various scenarios involving mixed numbers and strings:

• Product code generation: Combining numeric IDs with prefixes and suffixes
• Report formatting: Adding unit symbols before and after numbers
• Dynamic SQL construction: Embedding parameter values into SQL statement templates
• Log recording: Combining numerical metrics with descriptive text

By mastering correct type conversion and string concatenation techniques, developers can avoid common runtime errors and write more robust and maintainable T-SQL code.