October 26, 2011 at 5:04 am
I have a need to concatonate the values of ten fields together, but to only include a separator if there are values to separate.
E.g.
@Param1 = NULL
@Param2 = 'Value1'
@Param3 = NULL
@Param4 = NULL
@Param5 = 'Value2'
@Param6 = NULL
@Param7 = NULL
@Param8 = NULL
@Param9 = NULL
@Param10 = NULL
Return 'Value1; Value2'
The problem I have is than any solution I come up with is huge and complicated. I can't help but feel that I'm using a sledgehammer to crack a nut.
What would others suggest as an approach to this?
Stuart
October 26, 2011 at 5:27 am
COALESCE.
e.g.
DECLARE @Param1 VARCHAR(10) = NULL, @Param2 VARCHAR(10) = 'Value1', @Param3 VARCHAR(10) = NULL,
@Param4 VARCHAR(10) = NULL, @Param5 VARCHAR(10) = 'Value2', @Param6 VARCHAR(10) = NULL,
@Param7 VARCHAR(10) = NULL, @Param8 VARCHAR(10) = NULL, @Param9 VARCHAR(10) = NULL,
@Param10 VARCHAR(10) = NULL
DECLARE @Concatenation VARCHAR(100)
SELECT @Concatenation = COALESCE(@Concatenation,'') + COALESCE(ourValue + '; ', '')
FROM (VALUES (@Param1),(@Param2),(@Param3),(@Param4),(@Param5),(@Param6),
(@Param7),(@Param8),(@Param9),(@Param10)) a(ourValue)
--Had to mess a bit because you didn't include the separator at the end of the string
SELECT REVERSE(STUFF(REVERSE(@Concatenation),1,2,''))
October 26, 2011 at 6:27 am
Another possible solution:
DECLARE
@Param1 varchar(50),
@Param2 varchar(50),
@Param3 varchar(50),
@Param4 varchar(50),
@Param5 varchar(50),
@Param6 varchar(50),
@Param7 varchar(50),
@Param8 varchar(50),
@Param9 varchar(50),
@Param10 varchar(50)
SELECT
@Param1 = NULL,
@Param2 = 'Value1',
@Param3 = NULL,
@Param4 = NULL,
@Param5 = 'Value2',
@Param6 = NULL,
@Param7 = NULL,
@Param8 = NULL,
@Param9 = NULL,
@Param10 = NULL
DECLARE @concat varchar(max)
SELECT @concat = STUFF((
SELECT ';' + value AS [text()]
FROM (
VALUES (@Param1), (@Param2), (@Param3), (@Param4), (@Param5),
(@Param6), (@Param7), (@Param8), (@Param9), (@Param10)
) AS src (value)
WHERE value IS NOT NULL
FOR XML PATH('')
), 1, 1, SPACE(0));
SELECT @concat
-- Gianluca Sartori
October 26, 2011 at 6:34 am
Why SPACE(0) instead of ''?
P.S. Nice solution ;-).
October 26, 2011 at 7:06 am
Looks much better Gianluca, will do some testing later on to see how it scales but I suspect that it's going into my "toolbox" 🙂
October 26, 2011 at 7:07 am
Both do the job nicely and are MUCH simpler than anything I was coming up with (although the second has the consequence that it converts 'fish; & ; chips' into 'fish; &_amp; ; chips'... presumably a consequence of the xml).
Seeing as you've been so helpful already, I'm going to ask a bit more 😛
If I were to now convert this into a function where the number of input parameters would be up to ten, how would you approach this. I can't figure out optional parameters in functions other than to input all ten parameters, like:
functionname(@Param1, @Param2, @Param3, null, null, null, null, null, null, null)
Would that be as simple a solution as any?
Thanks
Stuart
[Edit to get & showing more meaningfully]
October 26, 2011 at 7:12 am
phual (10/26/2011)
Both do the job nicely and are MUCH simpler than anything I was coming up with (although the second has the consequence that it converts 'fish; & ; chips' into 'fish; &_amp; ; chips'... presumably a consequence of the xml).Seeing as you've been so helpful already, I'm going to ask a bit more 😛
If I were to now convert this into a function where the number of input parameters would be up to ten, how would you approach this. I can't figure out optional parameters in functions other than to input all ten parameters, like:
functionname(@Param1, @Param2, @Param3, null, null, null, null, null, null, null)
Would that be as simple a solution as any?
Thanks
Stuart
[Edit to get & showing more meaningfully]
Functions can't have optional parameters, you'd either need to have "NULL" passed in or "DEFAULT" if a default value is set in the function.
October 26, 2011 at 7:15 am
You can try with
FOR XML PATH.
FOR XML PATH TYPE
I've seen that suggested in the past. I don't have a full understanding of what it does so I recommend you read up on that before using it at all.
October 26, 2011 at 7:19 am
Modify the query as follows:
SELECT @concat = STUFF((
SELECT ';' + value AS [text()]
FROM (
VALUES (@Param1), (@Param2), (@Param3), (@Param4), (@Param5),
(@Param6), (@Param7), (@Param8), (@Param9), (@Param10)
) AS src (value)
WHERE value IS NOT NULL
FOR XML PATH(''), TYPE
).value('.[1]','varchar(1000)'), 1, 1, SPACE(0));
The only changes are adding "TYPE", to make it return typed XML, and then using the XQuery function "value" to turn it back into a string.
That will allow it to keep ampersands and such intact.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 7:19 am
Ninja's_RGR'us (10/26/2011)
You can try withFOR XML PATH.
I've seen that suggested in the past. I don't have a full understanding of what it does so I recommend you read up on that before using it at all.
Not sure I understand, but would like to. Do you mean passing in the parameters as one parameter, then splitting the string up in the function to assign to each of the 10 actual parameters?
October 26, 2011 at 7:21 am
As far as a variable number of parameter values, if you're actually on SQL 2008, as suggested by the forum this question is in, have you looked into Table Value Parameters?
Data here: http://msdn.microsoft.com/en-us/library/bb510489.aspx
With those, you can pass in as many or few values as you need to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 7:22 am
Cadavre (10/26/2011)
Ninja's_RGR'us (10/26/2011)
You can try withFOR XML PATH.
I've seen that suggested in the past. I don't have a full understanding of what it does so I recommend you read up on that before using it at all.
Not sure I understand, but would like to. Do you mean passing in the parameters as one parameter, then splitting the string up in the function to assign to each of the 10 actual parameters?
Was missing 1 word.
There's no optional parameters in function.
Since the problem is how to concatenated I don't see how forcing a concatenate first before split+concatenating actually helps :hehe:.
October 26, 2011 at 7:24 am
Ninja's_RGR'us (10/26/2011)
Cadavre (10/26/2011)
Ninja's_RGR'us (10/26/2011)
You can try withFOR XML PATH.
I've seen that suggested in the past. I don't have a full understanding of what it does so I recommend you read up on that before using it at all.
Not sure I understand, but would like to. Do you mean passing in the parameters as one parameter, then splitting the string up in the function to assign to each of the 10 actual parameters?
Was missing 1 word.
There's no optional parameters in function.
Since the problem is how to concatenated I don't see how forcing a concatenate first before split+concatenating actually helps :hehe:.
Think you were responding to a different part of the OPs post than I thought, which is what confused me.
October 26, 2011 at 7:25 am
Cadavre (10/26/2011)
Ninja's_RGR'us (10/26/2011)
Cadavre (10/26/2011)
Ninja's_RGR'us (10/26/2011)
You can try withFOR XML PATH.
I've seen that suggested in the past. I don't have a full understanding of what it does so I recommend you read up on that before using it at all.
Not sure I understand, but would like to. Do you mean passing in the parameters as one parameter, then splitting the string up in the function to assign to each of the 10 actual parameters?
Was missing 1 word.
There's no optional parameters in function.
Since the problem is how to concatenated I don't see how forcing a concatenate first before split+concatenating actually helps :hehe:.
Think you were responding to a different part of the OPs post than I thought, which is what confused me.
Absolutely!
October 26, 2011 at 7:35 am
Sample of TVP-use:
USE ProofOfConcept;
GO
CREATE TYPE TVPtest AS TABLE (Val VARCHAR(100));
GO
CREATE FUNCTION dbo.ConcatString
(@Vals_in AS TVPtest READONLY)
RETURNS VARCHAR(1000)
AS
BEGIN
RETURN STUFF((SELECT ';' + Val
FROM @Vals_in
FOR XML PATH(''), TYPE).value('.[1]','varchar(1000)'), 1, 1, '');
END;
GO
DECLARE @Vals AS TVPtest;
INSERT INTO @Vals(Val)
VALUES ('Val1'),('Val2 & Val3'),('Val4');
SELECT dbo.ConcatString (@Vals);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply