How to Concatenate Numbers and Strings to Format Numbers in T-SQL?

Question

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

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.

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

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?

when you try to concatenate varchar or Nvarchar with other Data type it may cause the error try to convert data types `@Actual_Dims_Lenght,@Actual_Dims_Width,@Actual_Dims_Height` using `Cast or Convert built in functions` — Upender Reddy, Jun 27 '23 at 05:52

Tom H · Accepted Answer · 2015-12-29T21:46:02.240

224

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

edited Dec 29 '15 at 21:46

answered Jun 04 '09 at 15:42

Tom H

46,766
14
87
128

7

You should always specify the length of a varchar: https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length – Eugene Ryabtsev Sep 24 '12 at 04:25
Thanks. A bit late, but I've added the length parameters. – Tom H Dec 29 '15 at 21:46

score 74 · Answer 2 · answered Jan 17 '16 at 11:00

74

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)

answered Jan 17 '16 at 11:00

Pரதீப்

91,748
19
131
172

I have come across some claims that `CONCAT()` performs faster than `CAST()`. Reliable sources of performance studies would be useful. – Codes with Hammer Jun 14 '18 at 12:42
I wouldn't be surprised if it was measurable in a benchmark, but I would be very surprised if it made any impact in normal queries. You'd have to have an enormous number of casts to drown out the normal operating time. – SilverbackNet Mar 01 '19 at 16:46

score 9 · Answer 3 · answered Jun 04 '09 at 15:42

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!

score 8 · Answer 4 · answered May 16 '16 at 21:40

8

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

answered May 16 '16 at 21:40

Sachin T Sawant

749
7
4

8

I don't understand how this addresses the question. – Codes with Hammer Jun 14 '18 at 12:42

score 4 · Answer 5 · answered Jun 04 '09 at 15:39

4

Cast the integers to varchar first!

answered Jun 04 '09 at 15:39

John Saunders

160,644
26
247
397

Exactly. The string 'x' completely hammers the pointless int decls; there is no arithmetic in the UDF. – bvj Jul 18 '14 at 06:25

p.campbell · Answer 6 · 2012-05-01T23:05:09.747

4

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

edited May 01 '12 at 23:05

answered Jun 04 '09 at 15:42

p.campbell

98,673
67
256
322

3

The STR function by default pads the number to 10 characters so it adds loads of spaces. e.g. `STR(1)` gives me 9 spaces followed by `1`. `CAST` actually works better. – Tahir Hassan Nov 05 '12 at 11:05

score 3 · Answer 7 · answered Jun 04 '09 at 15:42

3

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.

answered Jun 04 '09 at 15:42

Alex Martelli

854,459
170
1,222
1,395

score 2 · Answer 8 · edited Mar 25 '14 at 07:17

2

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

edited Mar 25 '14 at 07:17

Tushar Gupta - curioustushar

58,085
24
103
107

answered Mar 25 '14 at 06:56

RickyRam

181
1
1
10

score 2 · Answer 9 · answered Feb 11 '16 at 11:11

2

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)

answered Feb 11 '16 at 11:11

singhswat

832
7
20

score 1 · Answer 10 · answered Jun 04 '09 at 15:42

1

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

answered Jun 04 '09 at 15:42

Andomar

232,371
49
380
404

score 0 · Answer 11 · answered Jun 27 '23 at 04:19

0

set @values=concat(string,number,string)

answered Jun 27 '23 at 04:19

gulsherkhan shams

17
1

How to Concatenate Numbers and Strings to Format Numbers in T-SQL?

11 Answers11

Linked

Related