如何连接数字和字符串以格式化T-SQL中的数字?
我有以下功能如何连接数字和字符串以格式化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
我想要做的是,如果ActualWeight不为空则返回ActualWeight为“实际重量/ DIMS”或者使用的Actual_Dims_Lenght,宽度和高度。
如果是DIMS,那么我想格式化输出为LenghtxWidhtxHeight(15x10x4)。 ActualWeight,Adcutal_Dims_Lenght,Width和Height都是int(整数)值,但“Actual Weight/DIMS”的输出应该是varchar(50)。
我在哪里弄错了?
感谢
编辑:用户只能挑重量或DIMS ASP.net页面上,如果用户选择DIMS那么他们必须提供长度,宽度和高度。否则它会在ASP.net页面上显示错误。我应该担心在SQL方面呢?
一对夫妇的快速说明:
- 它在您的查询“长度”不“lenght”
- 表的别名可能会使其有更多可读现在
到问题...
在尝试连接它们之前,您需要显式地将参数转换为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))
更改此:
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。全面了解CAST和CONVERT here,因为数据类型很重要!
当试图将它们连接成一个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 Server 2008中,您可以使用STR
功能:
SELECT @ActualWeightDIMS = STR(@Actual_Dims_Lenght)
+ 'x' + STR(@Actual_Dims_Width)
+ 'x' + STR(@Actual_Dims_Height);
默认情况下,STR功能会将数字填充为10个字符,因此会增加空间负荷。例如`STR(1)`给了我9个空格,然后是`1`。 CAST实际上效果更好。 – 2012-11-05 11:05:45
你需要转换你的数值数据为字符串你做字符串连接之前,因此,例如使用而不仅仅是@Actual_Dims_Lenght
,& c。
尝试铸造整数为varchar,将其添加到字符串之前:
我试过下面的查询它的作品对我来说正好
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
如果你正在使用SQL Server 2012+你可以使用CONCAT函数,我们不必做任何明确的转换
SET @ActualWeightDIMS = Concat(@Actual_Dims_Lenght, 'x', @Actual_Dims_Width, 'x'
, @Actual_Dims_Height)
还有,你可能会用科学的号码,当您转换整型STR最后的机会......更安全的方法是
SET @ActualWeightDIMS = STR(@Actual_Dims_Width); OR Select STR(@Actual_Dims_Width) + str(@Actual_Dims_Width)
select 'abcd' + ltrim(str(1)) + ltrim(str(2))
您应该始终指定varchar的长度:http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx – 2012-09-24 04:25:02