sql数字转字符串 server字符串转时间




如何连接数字和字符串以格式化T-SQL中的数字? (10)

我有以下功能

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不为null,则返回ActualWeight作为“Actual Weight / DIMS”,否则使用Actual_Dims_Lenght,Width和Height。

如果它是DIMS,那么我想格式化输出为LenghtxWidhtxHeight(15x10x4)。 ActualWeight,Adcutal_Dims_Lenght,Width和Height都是int(整数)值,但“Actual Weight / DIMS”的输出应该是varchar(50)。

我在哪里错了?

谢谢

编辑:用户只能在ASP.net页面选择Weight或DIMS,如果用户选择了DIMS,那么他们必须提供长度,宽度和高度。 否则它会在ASP.net页面上显示错误。 我应该担心在SQL方面呢?

https://code.i-harness.com


先将整数转换为varchar!


几个快速注释:

  • 这是“长度”而不是“长度”
  • 查询中的表别名可能会使其更具可读性

现在解决问题...

在尝试连接它们之前,您需要显式地将参数转换为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))

在将它们添加到字符串之前,尝试将它们转换为varchar。

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

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


如果你使用的是SQL Server 2012+,你可以使用CONCAT函数,我们不需要做任何显式的转换

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

尝试将它们连接成一个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);

当您将Integer转换为Str时,您可能最终得到Scientific Number ...更安全的方法是

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


我尝试了下面的查询它完全适用于我

 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

改变这个:

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的所有信息,因为数据类型很重要!


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




tsql