How to Avoid Conversion Errors When Concatenating Numbers and Strings in T-SQL?

How to Concatenate Numbers and Strings for Number Formatting in T-SQL

Problem Description

In T-SQL, a common task is to combine numbers and strings to format a field. For instance, you may need to display weights or dimensions using specific units. However, when combining integers and strings, conversion errors can arise.

Code Example

Consider the following code snippet that aims to concatenate and format numbers for weights and dimensions:

ALTER FUNCTION [dbo].[ActualWeightDIMS]
(
    @ActualWeight int,
    @Actual_Dims_Lenght int,
    @Actual_Dims_Width int,
    @Actual_Dims_Height int
)
RETURNS varchar(50)
AS
BEGIN

DECLARE @ActualWeightDIMS varchar(50);
--Actual Weight
     IF (@ActualWeight is not null) 
          SET @ActualWeightDIMS = @ActualWeight;
--Actual DIMS
     IF (@Actual_Dims_Lenght is not null) AND 
          (@Actual_Dims_Width is not null) AND (@Actual_Dims_Height is not null)
          SET @ActualWeightDIMS= @Actual_Dims_Lenght + 'x' + @Actual_Dims_Width + 'x' + @Actual_Dims_Height;


     RETURN(@ActualWeightDIMS);

END

Error Encountered

When using the function, an error occurs: "Conversion failed when converting the varchar value 'x' to data type int." This happens when concatenating integers without explicitly converting them to strings.

Solution

To resolve the error, explicitly cast the integer parameters to VARCHAR before concatenation:

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 ensures that the parameters are treated as strings, allowing for their concatenation with the 'x' delimiter.

The above is the detailed content of How to Avoid Conversion Errors When Concatenating Numbers and Strings in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!