숫자와 문자열을 연결하여 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가 null이 아닌 경우 "Actual Weight / DIMS"에 대한 ActualWeight를 반환하거나 Actual_Dims_Lenght, Width 및 Height를 사용하는 것입니다.

DIMS 인 경우 출력을 LenghtxWidhtxHeight (15x10x4)로 포맷하고 싶습니다. ActualWeight, Adcutal_Dims_Lenght, Width 및 Height는 모두 int (정수) 값이지만 "Actual Weight / DIMS"의 출력은 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?

---

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:

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

---

If you are using SQL Server 2012+ you can use 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) 

---

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!

---

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

---

You must cast your integers as string when trying to concatenate them into a 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:

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

---

Cast the integers to varchar first!

---

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.

---

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

---

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

---

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

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

참고URL : https://stackoverflow.com/questions/951320/how-to-concatenate-numbers-and-strings-to-format-numbers-in-t-sql