如何连接数字和字符串来格式化T-SQL中的数字?

I have the following function

我有下面的函数

ALTER FUNCTION [dbo].[ActualWeightDIMS]
(
    -- Add the parameters for the function here
    @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

but when i tried to use it, i got the following error "Conversion failed when converting the varchar value 'x' to data type int." when i use the following select statement

但是当我尝试使用它时，当我使用下面的select语句时，我得到了以下错误“在将varchar值'x'转换为数据类型int时失败”

select 
 BA_Adjustment_Detail.ID_Number [ID_Number],
 BA_Adjustment_Detail.Submit_Date [Submit_Date],
 BA_Category.Category [category],
 BA_Type_Of_Request.Request [Type_Of_Request],
 dbo.ActualWeightDIMS(BA_Adjustment_Detail.ActualWeight,BA_Adjustment_Detail.Actual_Dims_Lenght,BA_Adjustment_Detail.Actual_Dims_Width,BA_Adjustment_Detail.Actual_Dims_Height) [Actual Weight/DIMS],
 BA_Adjustment_Detail.Notes [Notes],
 BA_Adjustment_Detail.UPSCustomerNo [UPSNo],
 BA_Adjustment_Detail.TrackingNo [AirbillNo],
 BA_Adjustment_Detail.StoreNo [StoreNo],
 BA_Adjustment_Detail.Download_Date [Download_Date],
 BA_Adjustment_Detail.Shipment_Date[ShipmentDate],
 BA_Adjustment_Detail.FranchiseNo [FranchiseNo],
 BA_Adjustment_Detail.CustomerNo [CustomerNo],
 BA_Adjustment_Detail.BillTo [BillTo],
 BA_Adjustment_Detail.Adjustment_Amount_Requested [Adjustment_Amount_Requested]
from BA_Adjustment_Detail
inner join BA_Category 
on BA_Category.ID = BA_Adjustment_Detail.CategoryID
inner join BA_Type_Of_Request
on BA_Type_Of_Request.ID = BA_Adjustment_Detail.TypeOfRequestID

What I want to do is if the ActualWeight is not null then return the ActualWeight for the "Actual Weight/DIMS" or else use the Actual_Dims_Lenght, Width and Height.

我要做的是，如果实际权重不是null，那么返回“实际权重/DIMS”的实际权重，或者使用Actual_Dims_Lenght、Width和Height。

If it is DIMS then i want to format the output to be LenghtxWidhtxHeight (15x10x4). The ActualWeight, Adcutal_Dims_Lenght, Width and Height are all int (integer) value but the output for "Actual Weight/DIMS" should be varchar(50).

如果是DIMS，那么我希望将输出格式化为LenghtxWidhtxHeight (15x10x4)。实际重量、Adcutal_Dims_Lenght、宽度和高度都是int (integer)值，但“实际重量/DIMS”的输出应该是varchar(50)。

Where am i getting it wrong?

我哪里搞错了?

thank

谢谢

edit: The user can only pick either Weight or DIMS on ASP.net page and if user selected DIMS then they must supply Length, Width and Height. Else it will throw error on the ASP.net page. Should i worry about it on the sql side?

编辑:用户只能在ASP.net页面上选择重量或下沉量，如果用户选择下沉量，则必须提供长度、宽度和高度。否则它会在ASP.net页面上抛出错误。我应该在sql端担心它吗?

10 个解决方案

#1

A couple of quick notes:

几个简短的笔记:

• It's "length" not "lenght"
• 它的“长度”而不是“长度”
• Table aliases in your query would probably make it a lot more readable
• 查询中的表别名可能会使其更具可读性

Now onto the problem...

现在到这个问题…

You need to explicitly convert your parameters to VARCHAR before trying to concatenate them. When SQL Server sees @my_int + 'X' it thinks you're trying to add the number "X" to @my_int and it can't do that. Instead try:

在尝试连接它们之前，您需要显式地将参数转换为VARCHAR。当SQL Server看到@my_int + 'X'时，它认为您正在尝试将数字“X”添加到@my_int，但它不能这样做。而不是尝试:

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))

#2

If you are using SQL Server 2012+ you can use CONCAT function in which we don't have to do any explicit conversion

如果您正在使用SQL Server 2012+，您可以使用CONCAT函数，其中我们不需要进行任何显式转换

SET @ActualWeightDIMS = Concat(@Actual_Dims_Lenght, 'x', @Actual_Dims_Width, 'x' 
                        , @Actual_Dims_Height) 

#3

select 'abcd' + ltrim(str(1)) + ltrim(str(2))

#4

Change this:

改变:

SET @ActualWeightDIMS= @Actual_Dims_Lenght + 'x' + 
    @Actual_Dims_Width + 'x' + @Actual_Dims_Height;

To this:

:

SET @ActualWeightDIMS= CAST(@Actual_Dims_Lenght as varchar(3)) + 'x' + 
    CAST(@Actual_Dims_Width as varchar(3)) + 'x' + 
    CAST(@Actual_Dims_Height as varchar(3));

Change this:

改变:

SET @ActualWeightDIMS = @ActualWeight;

To this:

:

SET @ActualWeightDIMS = CAST(@ActualWeight as varchar(50));

You need to use CAST. Learn all about CAST and CONVERT here, because data types are important!

您需要使用CAST。在这里学习所有关于CAST和CONVERT的知识，因为数据类型很重要!

#5

You must cast your integers as string when trying to concatenate them into a varchar.

当试图将整数连接到varchar中时，必须将其转换为字符串。

i.e.

即。

 SELECT  @ActualWeightDIMS = CAST(@Actual_Dims_Lenght AS varchar(10)) 
                              + 'x' + 
                             CAST(@Actual_Dims_Width as varchar(10)) 
                             + 'x' + CAST(@Actual_Dims_Height as varchar(10));

In SQL Server 2008, you can use the STR function:

在SQL Server 2008中，可以使用STR函数:

   SELECT  @ActualWeightDIMS = STR(@Actual_Dims_Lenght) 
                              + 'x' + STR(@Actual_Dims_Width) 
                              + 'x' + STR(@Actual_Dims_Height);

#6

Cast the integers to varchar first!

首先将整数转换为varchar !

#7

You need to CAST your numeric data to strings before you do string concatenation, so for example use CAST(@Actual_Dims_Lenght AS VARCHAR) instead of just @Actual_Dims_Lenght, &c.

在进行字符串连接之前，您需要将数字数据转换为字符串，因此，例如使用CAST(@Actual_Dims_Lenght作为VARCHAR)，而不仅仅是@Actual_Dims_Lenght， &c。

#8

I was tried the below query it's works for me exactly

我尝试了下面的查询，它完全适合我

 with cte as(

   select ROW_NUMBER() over (order by repairid) as'RN', [RepairProductId] from [Ws_RepairList]
  )
  update CTE set [RepairProductId]= ISNULL([RepairProductId]+convert(nvarchar(10),RN),0) from cte

#9

There are chances that you might end up with Scientific Number when you convert Integer to Str... safer way is

当你把整数转换成Str时，你可能会得到一个科学的数字。更安全的方法是

SET @ActualWeightDIMS = STR(@Actual_Dims_Width); OR Select STR(@Actual_Dims_Width) + str(@Actual_Dims_Width)

设置@ActualWeightDIMS = STR(@Actual_Dims_Width);或者选择STR(@Actual_Dims_Width) + STR(@Actual_Dims_Width)

#10

Try casting the ints to varchar, before adding them to a string:

试着在varchar上投射ints，然后将它们添加到字符串中:

SET @ActualWeightDIMS = cast(@Actual_Dims_Lenght as varchar(8)) + 
   'x' + cast(@Actual_Dims_Width as varchar(8)) + 
   'x' + cast(@Actual_Dims_Height as varhcar(8))