0

Most of the questions regarding string concatenation in SQL server is asked in scope of concatenation of multiple rows. However, this is a bit different - say you have N variables @str1, @str2, @str3... @strN to join.

How would one efficiently concatenate them using a delimiter?

Example:

DECLARE 
    @str1 nvarchar(60), 
    @str2 nvarchar(60), 
    @str3 nvarchar(60), 
    @str4 nvarchar(60), 
    @str5 nvarchar(60),
    @strJoint nvarchar(max)

SELECT 
    @str1 = 'A',
    @str2 = 'B',
    @str3 = 'C',
    @str4 = 'D',
    @str5 = 'E'

-- This does not exist out of the box
SELECT @strJoint = STRINGJOIN(';', @str1, @str2, @str3, @str4, @str5)

-- @strJoint should be 'A;B;C;D;E'
OzrenTkalcecKrznaric
  • 5,535
  • 4
  • 34
  • 57
  • Possible duplicate: http://stackoverflow.com/questions/19432370/concat-ws-for-sql-server – Deep Kalra Nov 02 '15 at 09:36
  • 1
    theres no stringjoin function in SQL Server, are you looking for help to build something like or something else? – Simon Price Nov 02 '15 at 09:43
  • 3
    First, why do you want to do this? The best solution is to do this on the client, *not* in SQL. SQL is *not* good at string manipulation The fastest option is to use a SQLCLR procedure or aggregate. [Aaron Bertrand](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) describes and benchmarks each option. In fact, this article has been linked as an answer to several similar SO questions – Panagiotis Kanavos Nov 02 '15 at 09:44
  • Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) – Panagiotis Kanavos Nov 02 '15 at 09:47
  • Unfortunately, I can't edit my question to explain one solution I found because SO is just blocking my submits and giving me errors :( – OzrenTkalcecKrznaric Nov 02 '15 at 09:47
  • @SimonPrice: Exactly, I'm trying to build that functionality. And in the way it behaves optimally. – OzrenTkalcecKrznaric Nov 02 '15 at 09:48
  • @PanagiotisKanavos: if I would be able to do that on the client, I would. However, there is no client (ok, there is but it's not available for the modification). The article you are mentioning is exactly the thing I explained i my question I'm NOT trying to do - I'm not concatenating rows, but variables. – OzrenTkalcecKrznaric Nov 02 '15 at 09:51
  • @OzrenTkalcecKrznaric then what's the question? Why don't you simply use `+` ? Writing a UDF to do that for 3,4 or 5 variables would be trivial – Panagiotis Kanavos Nov 02 '15 at 09:57
  • The question was about the nonexisting function. When you use the function, it adds delimiters by itself, you don't have to think about whether some value is null or not, and the output is always a neat delimited string. Plain old `+` doesn't help here. – OzrenTkalcecKrznaric Nov 02 '15 at 10:06
  • @OzrenTkalcecKrznaric: Well, another option is CLR enabled sql function where you can use `string.join()` method of .net framework. – Krishnraj Rana Nov 02 '15 at 10:06
  • And the damn SO still doesn't allow me to edit OR answer my own question. It's really frustrating! – OzrenTkalcecKrznaric Nov 02 '15 at 10:06

0 Answers0