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
– Tricky12, Commented Aug 20, 2013 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
– Elias, Commented Aug 20, 2013 at 19:56
Do you have a plan for escaping or encoding | and ~ if they happen to occur in the data? — HABO
– HABO, Commented Aug 20, 2013 at 20:10
I planned to remove unwanted/unnecessary/harmful characters prior to creating this string. — Elias
– Elias, Commented Aug 20, 2013 at 20:12

i-one · Accepted Answer · 2013-08-20 20:12:58Z

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, 2013 at 20:12

answered Aug 20, 2013 at 19:59

i-one

5,1201 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elias Over a year ago

Wow, that was impressive. Very nice +1. I saw your previous submission and both were grade A.

Elias Over a year ago

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.

i-one Over a year ago

varchar(max) can hold up to ~2Gb, though from this topic (link) 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).

Steve Snow · Accepted Answer · 2013-08-20 20:25:45Z

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.

Collectives™ on Stack Overflow

SQL Query Into Delimited String In Stored Procedure

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related