如何在 T-SQL 中连接数字和字符串以格式化数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/951320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Concatenate Numbers and Strings to Format Numbers in T-SQL?
提问by Hyman
I have the following function
我有以下功能
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
but when i tried to use it, i got the following error "Conversion failed when converting the varchar value 'x' to data type int." when i use the following select statement
但是当我尝试使用它时，出现以下错误“将 varchar 值 'x' 转换为数据类型 int 时转换失败。” 当我使用以下选择语句时
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
What I want to do is if the ActualWeight is not null then return the ActualWeight for the "Actual Weight/DIMS" or else use the Actual_Dims_Lenght, Width and Height.
我想要做的是，如果 ActualWeight 不为空，则返回“实际重量/DIMS”的 ActualWeight，否则使用 Actual_Dims_Lenght、宽度和高度。
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).
如果是 DIMS，那么我想将输出格式设置为 LenghtxWidhtxHeight (15x10x4)。ActualWeight、Adcutal_Dims_Lenght、Width 和 Height 都是 int（整数）值，但“Actual Weight/DIMS”的输出应该是 varchar(50)。
Where am i getting it wrong?
我哪里弄错了？
thank
谢谢
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?
编辑：用户只能在 ASP.net 页面上选择 Weight 或 DIMS，如果用户选择了 DIMS，则他们必须提供长度、宽度和高度。否则它会在 ASP.net 页面上抛出错误。我应该在 sql 方面担心吗？
回答by Tom H
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:
在尝试连接它们之前，您需要将参数显式转换为 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))
回答by P?????
回答by Eric
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!
您需要使用 CAST。在此处了解有关 CAST 和 CONVERT 的所有信息，因为数据类型很重要！
回答by Sachin T Sawant
select 'abcd' + ltrim(str(1)) + ltrim(str(2))
回答by p.campbell
You must cast your integers as string when trying to concatenate them into a varchar.
尝试将整数连接成 varchar 时，您必须将它们转换为字符串。
i.e.
IE
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 STRfunction:
在 SQL Server 2008 中，您可以使用该STR函数：
SELECT @ActualWeightDIMS = STR(@Actual_Dims_Lenght)
+ 'x' + STR(@Actual_Dims_Width)
+ 'x' + STR(@Actual_Dims_Height);
回答by Alex Martelli
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.
在进行字符串连接之前，您需要将数字数据转换为字符串，因此例如使用CAST(@Actual_Dims_Lenght AS VARCHAR)而不仅仅是@Actual_Dims_Lenght, &c。
回答by John Saunders
Cast the integers to varchar first!
首先将整数转换为 varchar ！
回答by RickyRam
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
回答by singhswat
There are chances that you might end up with Scientific Number when you convert Integer to Str... safer way is
当您将 Integer 转换为 Str 时，您可能最终会得到科学数......更安全的方法是
SET @ActualWeightDIMS = STR(@Actual_Dims_Width); OR
Select STR(@Actual_Dims_Width) + str(@Actual_Dims_Width)
SET @ActualWeightDIMS = STR(@Actual_Dims_Width); OR
Select STR(@Actual_Dims_Width) + str(@Actual_Dims_Width)
回答by Andomar
Try casting the ints to varchar, before adding them to a string:
尝试将整数转换为 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))
