Learn more about SQL Server tools

   
   















































Using SQL Server Concatenation Efficiently

MSSQLTips author Sherlee Dizon By:   |   Read Comments (13)   |   Related Tips: More > Functions - System
Problem

In this article I would like to share some tips on using concatenation efficiently for application development. Here I would like to point out some things that we must consider and look at when concatenating values or fields in our queries or stored procedures.  Check out this tip to learn more.

Solution

String concatenation is appending one string to the end of another string. The SQL language allows us to concatenate strings, but the syntax varies according to which database system we are using. Concatenation can be used to join strings from different sources including column values, literal strings, output from user defined functions, scalar sub queries, etc.  Let's jump into how to concatenate strings.

How Does SQL Server Concatenate Strings?

From SQL Server 2008 R2 version and below the "+" (plus sign) is an operator used in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression or into another column.

From SQL Server 2008, 2008 R2 and 2012 versions "+=" (add equals sign) is another string concatenation operator, which can be used to concatenate two strings and sets the string to the result of the operation. This operator cannot be used without a variable otherwise the query execution fails.

Now SQL Server 2012 brings us CONCAT() a new function for concatenation. It returns a string that is the result of concatenating two or more string values. The new function implicitly converts all arguments to string types and then concatenates the inputs. It requires a minimum of two input values or the concatenation fails.

SQL Server Database Design Considerations

One of the principles of relational database design is that the fields of the data tables should reflect a single characteristic of the table's subject, which means that they should not contain concatenated strings. For example, to display the physical address of a certain employee, the data might include building subunit number, building name, street name, city name, province name, postal code, and country name, e.g., "Unit 2307 ABC Tower Salcedo St. Makati City, 1402, Philippines", which combines 7 fields.

However, the employees data table should not use one field to store that concatenated string; rather, the concatenation of the 7 fields should happen upon running the report or the application. The reason for such principles is that without them, the entry and updating of large volumes of data becomes error-prone and labor-intensive. Separately entering the city, ZIP code, and nation allows data-entry validation (such as detecting an invalid zip code). Then those separate items can be used for sorting or indexing the records, such as all with "Makati" as the city name.

Uses of String Concatenation in SQL Server

Concatenation can deliver result in a more readable format while maintaining data in separate columns for greater flexibility. Below are some uses of string concatenation in SQL Server:

  • To join strings from different sources including column values, literal strings, output from user defined functions or scalar sub queries.
  • For creating a comma separated file (.csv file) or a text file (.txt file).
  • For combining multiple column values into single column that can be separated by a comma, a single space or by another separator.
  • For combining numeric, date and varchar data types into a single column or any combination.
  • When we need to combine multiple string values into one long string where a comma, period or special character is added.
  • To concatenate multiple rows into a single string or column.

Considerations for using String Concatenation in a Query

1. Know where the query result set will be used.

  • When concatenation is desired in a report, it should be provided at the time of running the report.
  • When concatenation is desired for displaying a list of data on a web page or in an application (Windows form), it should be created at the code behind or in a class.
  • If the query result set will be used for generating reports using a reporting tool it is best to do the string concatenation using the reporting tool.
  • If the query result set will be used for displaying list of data for a web page or a particular application, concatenating column values or expressions from multiple rows are usually best done in a client side of the application language. However, you can do these using different approaches in Transact SQL, but avoiding such methods for long term solutions many be your best bet.

2. Know who will use or who will need the query result set.

  • If the user who will use the query result set is not familiar with the reporting tool or any application language, it is generally best to perform the concatenation or data formatting for them.

3. Know how large the strings to be concatenated are.

  • We must determine the maximum characters of the string we are going to concatenate. There's a limitation in concatenating strings with particular data types.

4. Know how big the data you need to query and to display is.

  • You must weigh the cost of the extra data returned versus the cost of processing the data.  Also keep in mind the maintenance implications both in SQL Server and in the front end application.

5. Know if the data you need can be a null value.

  • Always check on null values when concatenating multiple fields or values in your query to prevent incorrect output especially if you are not yet using MSSQL 2012. Make your query as flexible as possible because we may never know when our client or user will change its report requirements.

Sample Usage of SQL Concatenation in Multiple Database Platforms

Concatenation varies by database type and version.  Check out these examples to see code in action.

Database: Microsoft Access

Microsoft Access uses the "+" plus operator to perform concatenation. The example below appends the value in the FirstName column with a blank space i.e. ' ' and then appends the value from the LastName column. The resulting string is given an Alias of FullName so we can easily identify it in our result set.

        
SELECT FirstName + ' ' + LastName As FullName FROM Employees

Database: Oracle

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server and Access.

        
SELECT FirstName || ' ' || LastName As FullName FROM Employees

Database: MySQL

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL.

       
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Employees

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating characters

In this example I will concatenate 2 columns which are both using string characters with data type of nvarchar into one column.

 USE AdventureWorks;
SELECT GroupName + ' - ' + Name as AdventureWorksDept 
FROM [HumanResources].[Department]
ORDER BY GroupName 

Here's the result set.

Concatenation in SQL Server 2008 R2 and below with the plus sign

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating characters and numbers

In concatenating numbers we need to convert them into string. We can use CAST() or CONVERT() function to do that. In this example, I will calculate the sum of all vacation and sick leave hours per production department then concatenate a string to the total hours.

 USE AdventureWorks;
SELECT [Title], 
'Total Vacation Hours : ' + CONVERT(varchar(5),SUM([VacationHours])) AS VacationHours,
'Total Sick Leave Hours : ' + CONVERT(varchar(5),SUM([SickLeaveHours])) AS SickLeaveHour                 
FROM [AdventureWorks].[HumanResources].[Employee]
WHERE TITLE LIKE 'Production%'
GROUP BY [Title] 
ORDER BY SUM([VacationHours]),SUM([SickLeaveHours]) 

Here's the result set.

Concatenating a string and numbers in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating numbers and dates

Just like in concatenating numbers we need to convert dates into string as well. We can use CAST() or CONVERT() function too.

 USE AdventureWorks;
SELECT empDeptHist.[StartDate],HRDept.[Name] as DeptName, 
CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) AS EmpCount,
CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) + '  ' +  
CONVERT(varchar(12), empDeptHist.[StartDate], 101) as ConcatenatedNumberDate
FROM [HumanResources].[EmployeeDepartmentHistory] empDeptHist
LEFT OUTER JOIN [HumanResources].[Department] HRDept
 ON empDeptHist.[DepartmentID] = HRDept.[DepartmentID]
WHERE HRDept.[Name] = 'Sales'
GROUP BY empDeptHist.[StartDate],HRDept.[Name]

Here's the result set.

Concatenating Numbers and Dates in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating characters and dates

Here's the easiest way to concatenate string and a date.

SELECT 'Today is :' + SPACE(5) + CONVERT(varchar(12), GETDATE(), 101)  AS CurrentDate
    

Here's the result set.

Concatenating a string and date in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating with multiple values

Now let's try to concatenate different data types into one column.

 USE AdventureWorks;
SELECT  empDeptHist.[StartDate],HRDept.[Name] as DeptName, 
CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) AS EmpCount,
HRDept.[Name] + ' with  ' + CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) +
' employee hired for  ' +  CONVERT(varchar(12), empDeptHist.[StartDate], 101) as ConcatenatedValues
FROM [HumanResources].[EmployeeDepartmentHistory] empDeptHist
LEFT OUTER JOIN [HumanResources].[Department] HRDept
 ON empDeptHist.[DepartmentID] = HRDept.[DepartmentID]
WHERE HRDept.[Name] like 'Shipping%'
GROUP BY empDeptHist.[StartDate],HRDept.[Name]
 

Here's the result set.

Concatenating multiple data types in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating with NULL values

In this example I will show how to handle the NULL values IMPLICITY and EXPLICITLY. We can handle null values explicitly by using ISNULL() or the COALESCE() function.

USE AdventureWorks;
SELECT TOP 10 [Title],[FirstName],[MiddleName],[LastName],[Suffix]
,[Title]+' '+[FirstName]+' '+[MiddleName]+' '+[LastName]+' '+[Suffix] as HandlingNULLImplicitly
,ISNULL([Title],'') + ' ' + ISNULL([FirstName],'') + ' ' + ISNULL([MiddleName],'') 
+ ' ' + ISNULL([LastName],'') + ' ' + ISNULL([Suffix],'') as HandlingNULLexplicitly
,COALESCE([Title],'') + ' ' + COALESCE([FirstName],'') + ' ' + COALESCE([MiddleName],'') 
+ ' ' + COALESCE([LastName],'') + ' ' + COALESCE([Suffix],'') as UsingCoalesce     
FROM [AdventureWorks].[Person].[Contact]
WHERE [Title]='Mr.'
 

Here's the result set.

Concatating with NULL values in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating GUIDs

In this example I will try to concatenate rowguid which has a unique identifier data type.

USE AdventureWorks;
SELECT TOP 10 [FirstName], [LastName], [rowguid], len([rowguid]) as char_count
,CAST([rowguid] as varchar(36)) + ' ==> ' + [LastName] +  ', '+ [FirstName] as ConcatenatedValue
FROM [AdventureWorks].[Person].[Contact]

Here's the result set.

Concatenating GUIDs and additional data types in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating rows of values into a single column

In this example I will show how to concatenate the list of department of a specific group separated by a comma into one column only.

USE AdventureWorks;
SELECT [DepartmentID],[Name],[GroupName]   
FROM [AdventureWorks].[HumanResources].[Department]
WHERE [GroupName]= 'Executive General and Administration'
  
DECLARE @GroupDept VARCHAR(8000)  
SELECT  @GroupDept = COALESCE(@GroupDept + ', ', '') + Name 
FROM [AdventureWorks].[HumanResources].[Department]
WHERE [GroupName]= 'Executive General and Administration'
SELECT 'Executive General and Administration Group Departments are ' + @GroupDept + '.'  AS GroupDeptList

Here's the result set. To check if the concatenated values are correct I've included the list of department name for the selected group name.

Concatenating rows of values into a single column in SQL Server

Database: Microsoft SQL Server using the "+" (plus sign) syntax - Concatenating rows with FOR XML PATH

In this example I will summarize data into groups or list of values in two ways by using correlated sub query or by using CROSS APPLY.

USE AdventureWorks;
-- Using correlated subquery 
SELECT G.GroupName, 
STUFF( (SELECT ', ' + DN.Name
  FROM HumanResources.Department AS DN             
  WHERE DN.GroupName = G.GroupName
  ORDER BY G.GroupName
  FOR XML PATH('') ), 1, 1, '') AS dept_list
FROM HumanResources.Department AS G
GROUP BY GroupName
-- Using CROSS APPLY  
SELECT G.GroupName, 
STUFF(P.dept_list, 1, 1, '') AS dept_list
FROM HumanResources.Department AS G
CROSS APPLY (SELECT ', ' + DN.Name
   FROM HumanResources.Department AS DN            
   WHERE DN.GroupName = G.GroupName
   ORDER BY G.GroupName
   FOR XML PATH('') ) AS P (dept_list)             
GROUP BY GroupName,P.dept_list   

Here's the result set. This approach can be use in some reporting purposes to summarize normalized tables into groups or list of values. There are also some reporting and client side tools that support this directly. This method is often called the XML black box method. The PATH clause is used with the input string that indicates the name of the wrapper element that will be created. When the PATH clause is used with an empty string it is used as an input it results in skipping the wrapper element generation.

Concatenating rows with FOR XML PATH in SQL Server

Here's the query to validate the data is correct.

USE AdventureWorks;
SELECT GroupName, Name FROM HumanResources.Department
ORDER BY GroupName
Result set from a simple query to validate the data is correct

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating characters

In this example I will concatenate 2 columns which are both nvarchar data types into one column, but this time by using the new CONCAT function in SQL Server 2012.

           
USE AdventureWorks;
SELECT CONCAT([GroupName] , ' - ' , [Name]) as AdventureWorksDept2012
FROM [HumanResources].[Department]
ORDER BY [GroupName]

Here's the result set.

Microsoft SQL Server 2012 version using the new CONCAT() function when concatenating character columns

Keep in mind even though we are currently using Microsoft SQL Server 2012 we can still use the previous syntax which is "+" (plus sign).  It will produce the same results. There's nothing to worry about if you have to use or migrate your previous stored procedure created with the previous syntax.

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating characters and numbers

USE AdventureWorks;           
SELECT [Title], CONCAT('Total Vacation Hours : ', SUM([VacationHours])) AS VacationHours
,CONCAT('Total Sick Leave Hours : ', SUM([SickLeaveHours])) AS SickLeaveHour                 
FROM [AdventureWorks].[HumanResources].[Employee]
WHERE TITLE LIKE 'Production%'
GROUP BY [Title] 
ORDER BY SUM([VacationHours]),SUM([SickLeaveHours]) 

Here's the result set.

Use the CONCAT() function in Microsoft SQL Server 2012 to concatenate characters and numbers

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating numbers and dates

Here's how easy it is to concatenate numbers and dates in SQL Server 2012. For this example I try to concatenate the total count of employee hired per department based on their start date.

USE AdventureWorks;           
SELECT  empDeptHist.[StartDate],HRDept.[Name] as DeptName, 
CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) AS EmpCount,
CONCAT(COUNT(empDeptHist.[EmployeeID]) , '  ',  
CONVERT(varchar(12), empDeptHist.[StartDate], 101)) as ConcatenatedNumberDate
FROM [HumanResources].[EmployeeDepartmentHistory] empDeptHist
LEFT OUTER JOIN [HumanResources].[Department] HRDept
 ON empDeptHist.[DepartmentID] = HRDept.[DepartmentID]
WHERE HRDept.[Name] = 'Sales'
GROUP BY empDeptHist.[StartDate],HRDept.[Name] 

Here's the result set.

Concating numbers and dates in SQL Server 2012 with the CONCAT function

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating characters and dates

For this example I will concatenate a string to the current date and display it in one column.

                  
SELECT CONCAT('Today is :', SPACE(5), CONVERT(varchar(12), GETDATE(), 101))  AS CurrentDate

Here's the result set.

Use the ConCat() function in SQL Server 2012 with characters and dates

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating multiple data types

USE AdventureWorks;
SELECT  empDeptHist.[StartDate],HRDept.[Name] as DeptName, 
CONVERT(varchar(5),COUNT(empDeptHist.[EmployeeID])) AS EmpCount,
CONCAT(HRDept.[Name] , ' with  ', COUNT(empDeptHist.[EmployeeID]) , 
' employee hired for  ',  CONVERT(varchar(12), empDeptHist.[StartDate], 101)) as ConcatenatedValues
FROM [HumanResources].[EmployeeDepartmentHistory] empDeptHist
LEFT OUTER JOIN [HumanResources].[Department] HRDept
 ON empDeptHist.[DepartmentID] = HRDept.[DepartmentID]
WHERE HRDept.[Name] like 'Shipping%'
GROUP BY empDeptHist.[StartDate],HRDept.[Name]
 

Here's the result set.

SQL Server 2012 Multiple Data Type Concatenation with the CONCAT() function

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating NULL values

In this example I will show that the "+" plus sign still works in 2012 and how it differs to the new CONCAT() function.

USE AdventureWorks;
SELECT TOP 10 [Title],[FirstName],[MiddleName],[LastName],[Suffix]
,[Title]+' '+[FirstName]+' '+[MiddleName]+' '+[LastName]+' '+[Suffix] as HandlingNULLImplicitly
,ISNULL([Title],'') + ' ' + ISNULL([FirstName],'') + ' ' + ISNULL([MiddleName],'') + ' ' 
+ ISNULL([LastName],'') + ' ' + ISNULL([Suffix],'') as HandlingNULLexplicitly
,COALESCE([Title],'') + ' ' + COALESCE([FirstName],'') + ' ' + COALESCE([MiddleName],'') + ' ' 
+ COALESCE([LastName],'') + ' ' + COALESCE([Suffix],'') as UsingCoalesce
,CONCAT([Title] , ' ' + [FirstName] , ' ' , [MiddleName] , ' ' , [LastName] , ' ' , [Suffix]) as UsingCONCAT     
FROM [AdventureWorks].[Person].[Contact]
WHERE [Title]='Mr.'

Here's the result set. From the result set below notice that CONCAT function implicitly coverts all arguments to string types and then concatenate the inputs. The CONCAT function only requires a minimum of two input values else the concatenation fails.

SQL Server 2012 Concatenation of NULL values

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating GUIDS

In this example I will try to concatenate rowguid which has a unique identifier data type using the new function and the previous syntax for concatenation to be able to see their difference.

USE AdventureWorks;
SELECT TOP 10 [FirstName], [LastName], [rowguid], len([rowguid]) as char_count
,CAST([rowguid] as varchar(36)) + ' ==> ' + [LastName] +  ', '+ [FirstName]   as ConcatenatedValue
,CONCAT([rowguid] , ' ==> ' , [LastName] ,  ', '+ [FirstName])   as UsingCONCAT
FROM [AdventureWorks].[Person].[Contact]    

Here's the result set.

Concatenating GUIDs in SQL Server 2012 with the ConCat() function

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating rows of values into a single column

In this example I will show how to concatenate the list of department of a specific group separated by a comma into one column only.

USE AdventureWorks;
SELECT [DepartmentID],[Name],[GroupName]   
FROM [AdventureWorks].[HumanResources].[Department]
WHERE [GroupName]= 'Executive General and Administration' 
DECLARE @GroupDept VARCHAR(8000)  
SELECT  @GroupDept = CONCAT(@GroupDept,', ', Name )
FROM [AdventureWorks].[HumanResources].[Department]
WHERE [GroupName]= 'Executive General and Administration'
SELECT CONCAT('Executive General and Administration Group departments are ',  
@GroupDept , '.')  AS GroupDeptList
    

Here's the result set. To check if the concatenated values are correct I've included the list of department names for the selected group name.

Concatenating rows of values into a single column with the CONCAT() function

Database: Microsoft SQL Server 2012 using the CONCAT() function - Concatenating rows with FOR XML PATH

In this example I will summarize data into groups or list of values in two ways by using correlated sub query or by using CROSS APPLY.

USE AdventureWorks;       
-- Using correlated subquery
SELECT G.GroupName, 
STUFF( (SELECT CONCAT(', ' , DN.Name)
  FROM HumanResources.Department AS DN             
  WHERE DN.GroupName = G.GroupName
  ORDER BY G.GroupName
  FOR XML PATH('') ), 1, 1, '') AS dept_list
FROM HumanResources.Department AS G
GROUP BY GroupName
-- Using CROSS APPLY    
SELECT G.GroupName, 
STUFF(P.dept_list, 1, 1, '') AS dept_list_UsingCROSS_APPLY
FROM HumanResources.Department AS G
CROSS APPLY (SELECT CONCAT(', ' , DN.Name)
   FROM HumanResources.Department AS DN            
   WHERE DN.GroupName = G.GroupName
   ORDER BY G.GroupName
   FOR XML PATH('') ) AS P (dept_list)             
GROUP BY GroupName,P.dept_list  

Here's the result set. This approach can be use in some reporting purposes to summarize normalized tables into groups or list of values. There are also some reporting and client side tools that support this directly. This method is often called the XML black box method. The PATH clause is used with input string that indicates the name of the wrapper element that will be created. When PATH clause with an empty string is used as an input it results in skipping the wrapper element generation.

SQL Server 2012 Row Concatenation Using For XML Path

Here's the query to check the data.

USE AdventureWorks;
SELECT GroupName, Name 
FROM HumanResources.Department
ORDER BY GroupName
Department Group Listing

Conclusion

It is not enough that we know how to concatenate strings or values. We must also know where and when to use it. Also always take into consideration the end-user who will use the output. Various programming considerations are to be carefully considered to choose one method over another depending on the situations. Always check and remember the limitations of each approach. One of the most logical choices would be the availability of a built-in operator with optional configurable parameters that can perform the concatenation of the values depending on the data type.

Next Steps


Last Update: 10/18/2012


About the author
MSSQLTips author Sherlee Dizon
Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, February 26, 2014 - 9:17:38 AM - SanJay Bollina Read The Tip

Good One


Sunday, February 16, 2014 - 2:39:23 AM - Sherlee Read The Tip

 

Hi absolute beginer,

Sorry for late response I just saw your question today.

You can use either

select fname + ' ' + lname as "Author's Name" from author

or

SELECT CONCAT(fname,  ' ', lnameas "Author's Name" from author


Sunday, February 09, 2014 - 3:51:54 PM - absolute beginer Read The Tip

Hi, I have a very simple question as I am just learning how to use select statements in sql2012. How do I add a space betwen 2 colums when I use concatenation? When I use the following statement ; select fname + lname as "Autor's Name" from author, I get 

Author's Name

JOHNSMITH

intead I want JOHN SMITH.

Thanks.


Thursday, October 17, 2013 - 5:17:56 PM - Csaba Toth Read The Tip

Your example about addresses must affect many businesses, just like my company. I think usually there's a need both in the application user interface and in the reports to display concatenated "full address" fields.

What do you think about creating a computed FULL_ADDRESS column, maybe even index it and persist it? This way the report designer customers would have a ready to use column, as well as the application?

(I see Megan advises the same)

 


Thursday, October 17, 2013 - 12:06:47 PM - NB Read The Tip

Greetings,

Excellent article overall, but you do have a slight error here:

"To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server and Access."

|| in Oracle is not equivalent to + in SQL Server & Access because both SQL Server & Access default to treating + as a mathmatical operator if the columns or values you are trying to concatenate are int data types.  Oracle implicitly converts anything using || in a concatenation to a string in order to preserve their contents.

In this sense, Oracle's || is far superior to SQL Server & Access.


Wednesday, June 05, 2013 - 1:10:51 AM - Thiru Read The Tip

It is  nice  know about CONCAT  function in  SQL Server 2012


Tuesday, June 04, 2013 - 5:11:42 AM - John Read The Tip

Cool summary!


Wednesday, February 20, 2013 - 5:43:11 AM - Sherlee Read The Tip

 

Thanks Megan Brooks.

I haven't try that approach yet but I will when I need it.

 


Tuesday, November 06, 2012 - 11:48:19 AM - Megan Brooks Read The Tip

You can pre-concatenate a string built from fields, such as an address line, by adding it as a calculated field in the table containing the individual fields. You then have the option of saving the resulting string in the table to avoid the overhead of re-creating it each time (SQL Server recalculates when any of the input fields changes). This is especially useful if the concatenation process is expensive, such as when user-defined functions are invoked to clean up 'messy' source fields. Doing the work in the application would be better, but is not always an option.


Saturday, October 20, 2012 - 2:20:06 AM - Sherlee Read The Tip

 

Thanks TimothyAWiseman

Thank you Suman sure I will try whenever I got the time.


Friday, October 19, 2012 - 12:11:32 AM - patty boy ricarte Read The Tip

Thank you for the article, I particularly liked the comparisons with other RDBMS.

I would respectfully add that it often makes sense to let the presentation or application layers handle concatenation, but as you say it all depends on how the system is meant to work together.

I like your analogy on Concatenation. Please come up with more similar articles:)

Ilike also the explanation on sqlconcat.

 

 

more article pls.


Thursday, October 18, 2012 - 7:45:03 PM - Suman Read The Tip

I like your analogy on Concatenation. Please come up with more similar articles:)


Thursday, October 18, 2012 - 5:35:29 PM - TimothyAWiseman Read The Tip

Thank you for the article, I particularly liked the comparisons with other RDBMS.

I would respectfully add that it often makes sense to let the presentation or application layers handle concatenation, but as you say it all depends on how the system is meant to work together.

Also, Jeff Moden has an excellent somewhat related article on the effeciency of certain types of string concatenation in SQL Server.




 
Sponsor Information