Concatenating String in SQL Server Query

"+ " Operator can be used for string concatination in SQL Server.

Example 1:

SELECT
[Name]  + ' [' +[ProductNumber] + ']'

FROM [Production].[Product]

Example 2:

SELECT
PC.[Name] + ' - ' + PSC.[Name] + ' - ' + P.[Name]
FROM [Production].[Product] P
JOIN [Production].[ProductSubcategory] PSC
ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
JOIN [Production].[ProductCategory] PC

ON PC.[ProductCategoryID] = PSC.ProductSubcategoryID