Using SQL Server Concatenation Efficiently

By:   |   Comments (17)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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 and later brings us CONCAT() which is 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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: SQL Server 2012 and later 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, July 18, 2016 - 5:47:21 PM - Scott Back To Top (41910)

Using FOR XML PATH('') concatenation can create a string littered with XML entities: & < > or 

You can add ",TYPE" to the FOR XML clause to return XML instead of VARCHAR, and then use .value() to turn it back into VARCHAR with the XML entities removed.

Compare the resuls of these queries:

SELECT '&<
>'
FOR XML PATH('')

Result: &amp;&lt;&#0D;&gt;

SELECT (SELECT '&<
>'
FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)')

Result: &<  >  {in grid view}

&<     {in text view the line break is preserved}
>

Sunday, October 11, 2015 - 8:56:37 AM - Sherlee Back To Top (38857)

Hello tlee,

You should check the character length you defined to your varchar variable and also to your field where you put your concatenated data.

Thursday, October 8, 2015 - 9:15:30 AM - tlee Back To Top (38845)

Hi All, I need suggestions...when I concatenate my rows into one field, it is cutting off some of my data.  What am I doing incorrectly?

 

Thanks for any assistance!

Monday, April 27, 2015 - 4:45:39 AM - Thomas Franz Back To Top (37037)

I'm missing the most useful syntax - combining CONCAT and +:

CONCAT([Title]  + ' ',  [FirstName]  +  ' ', [MiddleName] + ' ' , [LastName] , ' ' + [Suffix])

or a shorter (and more common) example:

CONCAT(LastName, ', ' + FirstName)

By combining the behavior of both concanations you would eliminate any unwanted spaces / commas / whatever if any of the fields is NULL so that you will receive "Mr. Austin Thompson" instead of "Mr. Austin  Thompson " or in the second example - if the first name is missing - only the "Miller" and not "Miller, "

Wednesday, February 26, 2014 - 9:17:38 AM - SanJay Bollina Back To Top (29585)

Good One

Sunday, February 16, 2014 - 2:39:23 AM - Sherlee Back To Top (29466)

 

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,  ' ', lname) as "Author's Name" from author

Sunday, February 9, 2014 - 3:51:54 PM - absolute beginer Back To Top (29380)

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 Back To Top (27181)

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 Back To Top (27177)

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 5, 2013 - 1:10:51 AM - Thiru Back To Top (25295)

It is  nice  know about CONCAT  function in  SQL Server 2012

Tuesday, June 4, 2013 - 5:11:42 AM - John Back To Top (25271)

Cool summary!

Wednesday, February 20, 2013 - 5:43:11 AM - Sherlee Back To Top (22293)

 

Thanks Megan Brooks.

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

 

Tuesday, November 6, 2012 - 11:48:19 AM - Megan Brooks Back To Top (20231)

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 Back To Top (20003)

 

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 Back To Top (19992)

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 Back To Top (19989)

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

Thursday, October 18, 2012 - 5:35:29 PM - TimothyAWiseman Back To Top (19988)

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.














get free sql tips
agree to terms