如何在 T-SQL 中连接数字和字符串以格式化数字？

我有以下功能

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

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

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

我想做的是，如果实际重量不为空，然后返回“实际重量/DIMS”的实际重量或使用实际 _ DIMS _ 长度，宽度和高度。

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

我哪里说错了？

谢谢

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?

314929 次浏览
小开

Cast the integers to varchar first!

小开

改变这一点:

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

这样说:

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

改变这一点:

SET @ActualWeightDIMS = @ActualWeight;

To this:

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

您需要使用 CAST。学习有关 CAST 和 CONVERT 给你的所有知识，因为数据类型很重要！

小开

在尝试将整数连接到 varchar 时，必须将整数强制转换为字符串。

也就是说。

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

在 SQLServer2008中，可以使用 STR函数:

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

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

小开
最佳答案

A couple of quick notes:

  • It's "length" not "lenght"
  • Table aliases in your query would probably make it a lot more readable

现在说说问题。

在尝试连接参数之前，需要显式地将参数转换为 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))
小开

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

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

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
小开

If you are using SQLServer2012 + you can use < strong > CONCAT function in which we don't have to do any explicit conversion

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

有机会，你可能最终与科学数字当你转换为 Str... 更安全的方法是

SET@AccounalWeightDIMS = STR (@Actal _ Dims _ Width) ; OR Select STR(@Actual_Dims_Width) + str(@Actual_Dims_Width)

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

提交答案