How to Concatenate Numbers and Strings to Format Numbers in T-Sql

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

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

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

Concatenate a Varchar and int

SELECT ('VarValue' + CAST(32 AS VARCHAR))

Concatenate String + date in SQL Server

Convert the date, I am guessing you want the format mm-dd-yyyy, if so:

CONCAT([key],'-',[ID],'-',CONVERT(VARCHAR(10), [DATE], 110))

If you want dd-mm-yyyy it is:

CONVERT(VARCHAR(10), [DATE], 105)

Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Printing integer variable and string on same line in SQL

declare @x INT = 1 /* Declares an integer variable named "x" with the value of 1 */
    
PRINT 'There are ' + CAST(@x AS VARCHAR) + ' alias combinations did not match a record' /* Prints a string concatenated with x casted as a varchar */

---

Related Topics