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

User · Answer

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)

User · Answer

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   amp c

User · Answer

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

User · Answer

Cast the integers to varchar first

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select  abcd    ltrim str 1     ltrim str 2

User · Answer

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

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

Examples related to sql

Examples related to tsql