SQL Query Into Delimited String In Stored Procedure

Question

I have the table:

CREATE TABLE [address_tabletype] (
[CONTROL_NUMBER] [varchar](12) NULL,
[ADDRESS1] [varchar](50) NULL,
[ADDRESS2] [varchar](50) NULL,
[CITY] [varchar](50) NULL,
[STATE] [varchar](2) NULL,
[ZIP] [varchar](10) NULL
)
GO

and, say I have the following rows:

2506387 1   2   3   4   5
2506394 1   2   3   4   5
2506403 1   2   3   4   5

I would like to to look like:

2506387|1|2|3|4|5~2506394|1|2|3|4|5~2506403|1|2|3|4|5

I haven't tried anything because I honestly have no idea where to start.

I am using SQL Server 2008 R2, and will be using a temp table to build this string.

EDIT

I am using this to pass to a 3rd party CLR Function that absolutely needs a delimited string in this fashion. I planned to send it over to the function, return it to the SP, break it down to its original form, and then return the results to the caller of the SP.

Thank you.

Are you pulling this information out of the database? I ask because it would be much easier to manipulate these as strings once pulled from the database. — Tricky12, Aug 20 '13 at 19:54
Yes. This is within a stored procedure that will accept select statements built with the table definition. I tried 1000 different ways to get to this point, and this is the only applicable solution per my environment. — Elias, Aug 20 '13 at 19:56
Do you have a plan for escaping or encoding `|` and `~` if they happen to occur in the data? — HABO, Aug 20 '13 at 20:10
I planned to remove unwanted/unnecessary/harmful characters prior to creating this string. — Elias, Aug 20 '13 at 20:12

i-one · Accepted Answer · 2013-08-20T20:12:58.020

3

Try following:

SELECT STUFF((select (
    SELECT  '~' + (
        CONTROL_NUMBER+'|'+
        ADDRESS1 +'|'+
        ADDRESS2 + '|'+
        CITY + '|'+
        [States] + '|'+
        ZIP)
    FROM address_tabletype
    FOR XML PATH(''), type
    ).value('text()[1]', 'varchar(max)')), 1, 1, '')

edited Aug 20 '13 at 20:12

answered Aug 20 '13 at 19:59

i-one

5,050
1
28
40

Wow, that was impressive. Very nice +1. I saw your previous submission and both were grade A. – Elias Aug 20 '13 at 20:17
Does there happen to be anything larger than Varchar(Max)? I know that that is a huge value, but I'm only squeezing about 3500 addresses, per block, using this method. – Elias Aug 21 '13 at 13:02
varchar(max) can hold up to ~2Gb, though from this topic ([link](http://stackoverflow.com/questions/7611394/maximum-size-of-a-varcharmax-variable)) it seems that in SqlServer 2008 (and onwards probably) _variable_ (not column) can hold more. Unlikely your address records sized as ~0.6Mb each (2Gb/3500). So, I guess there may be some limitations within `for xml`. In this case you can try concatenating in small batches e.g. 3000 rows at a time in a loop. See sample at SQLFiddle ([link](http://sqlfiddle.com/#!6/956bb/6)). – i-one Aug 21 '13 at 14:31

score 0 · Answer 2 · answered Aug 20 '13 at 20:25

Basically I would use something like this in your SP:

declare your temp table variable like, note add all the fields you want to it:

declare @rowsToConcat table ([ID] int identity(1,1),[CONTROL_NUMBER] varchar(12), ...repeat for all of your fields)

insert into @rowsToConcat
    select * from [address_tabletype]
declare @loopcnt int
declare @concat_output as varchar(1000)
set @loopcnt = 1
while(@loopcnt < (select max([ID]) from @rowsToConcat))
begin
    select @concat_output = @concat_output + [CONTROL_NUMBER] + '|' + [..all other fields] + '~'
    from @rowsToConcat 
    where [ID] = @loopcnt
    set @loopcnt = @loopcnt+1
end

Just typed this without testing here, I did not fill in all your fields but put ..all other fields for you to repeat. I use this technique a lot works for me. Also haven't tested but you may want to make @concat_output a varchar(max) if you don't know how many results to expect.

SQL Query Into Delimited String In Stored Procedure

2 Answers2