如何在T-SQL中连接数字和字符串以格式化数字？
如何在T-SQL中连接数字和字符串以格式化数字？
提问于 2009-06-04 15:34:11
我有以下函数
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但是当我尝试使用它时，我得到了以下错误："Conversion failed when the varchar value 'x‘to data type 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我想做的是，如果ActualWeight不为空，则返回“实际重量/尺寸”的ActualWeight，或者使用Actual_Dims_Lenght、宽度和高度。
如果它是DIMS，那么我想将输出格式化为LenghtxWidhtxHeight (15x10x4)。ActualWeight、Adcutal_Dims_Lenght、Width和Height都是int (整数)值，但是“实际重量/尺寸”的输出应该是varchar(50)。
我哪里搞错了？
谢谢
编辑:用户只能在ASP.net页面上选择重量或尺寸，如果用户选择了尺寸，则必须提供长度、宽度和高度。否则它将在ASP.net页面上抛出错误。我应该担心sql方面的问题吗？
回答 8
Stack Overflow用户
发布于 2009-06-04 15:42:10
更改此设置：
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;要这样做：
SET @ActualWeightDIMS = CAST(@ActualWeight as varchar(50));您需要使用CAST。了解所有关于强制转换和转换here的知识，因为数据类型很重要！
Stack Overflow用户
发布于 2016-05-17 05:40:00
select 'abcd' + ltrim(str(1)) + ltrim(str(2))Stack Overflow用户
发布于 2009-06-04 15:42:36
在尝试将整数连接到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));在SQL Server2008中，您可以使用STR函数：
SELECT @ActualWeightDIMS = STR(@Actual_Dims_Lenght)
+ 'x' + STR(@Actual_Dims_Width)
+ 'x' + STR(@Actual_Dims_Height);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/951320
复制相关文章
相似问题