Sort a concatenated string in sql server 2005


 i have a string like 'd','a',c' from a query resultset.
Now, i need to sort this string and again get a concatenated output like 'a','c','d'  . How do i do it ?

i thought of passing the original value to a stored procedure or a function.. the stored proc/func will split & store the value in a temp table such that each value is a new row..


I will then query this temp table and get the sorted values..

The problem i am facing is that if i use a function, i cannot do any t-sql statements within a function and if i use a stored procedure, i cannot call it inline..
Then i thought, i will create both.. the stored procedure will do the sorting and storing the sorted value in the temp table and i will call the stored procedure within the function and make it inline.. (ofcourse there will be 2 parameters... (i) the string and (ii) the delimiter)..
but alas, this too does not work.. i cannot call a stored procedure from within a function..:-(

Could anyone suggest a workaround to achieve this ???


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You will have to add vw_Nums which is included.
if object_id('[dbo].[fn_ResortDelimited]') is not null
     drop function [dbo].[fn_ResortDelimited]
create function [dbo].[fn_ResortDelimited](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns nvarchar(max)
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
declare @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
declare @Output nvarchar(max)
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
select @Output = isnull(@Output + @Delimiter,'') + theValue
from @Values
order by theValue
return @Output
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_ResortDelimited, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
Sample Usage:
select * from [dbo].[fn_ResortDelimited]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_ResortDelimited]('a','|')
select * from [dbo].[fn_ResortDelimited]('a basdf c d',' ')
select [dbo].[fn_ResortDelimited]('d,a,c',',')

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
acptAuthor Commented:
Excellent... A +++

worked exactly as I wanted it to be..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.