CODEWITHSUNDEEP
sqlt-sql
Jack
Jack

Reputation: 10047

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

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?

Upvotes: 121

Views: 339746

Answers (11)

gulsherkhan shams
gulsherkhan shams

Reputation: 17

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

Upvotes: 0

Sachin T Sawant
Sachin T Sawant

Reputation: 759

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

Upvotes: 8

singhswat
singhswat

Reputation: 906

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)

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93754

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)

Upvotes: 80

Tom H
Tom H

Reputation: 47402

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

Upvotes: 228

RickyRam
RickyRam

Reputation: 181

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

Upvotes: 2

p.campbell
p.campbell

Reputation: 100647

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

Upvotes: 4

Andomar
Andomar

Reputation: 238296

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

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 882741

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.

Upvotes: 3

Eric
Eric

Reputation: 95223

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!

Upvotes: 10

John Saunders
John Saunders

Reputation: 161831

Cast the integers to varchar first!

Upvotes: 4

Related Questions