Sort a concatenated string in sql server 2005

Hi,

 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..

TmpSort
--------------
d
a
c

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 ???

Thanks..

acptAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
You will have to add vw_Nums which is included.
if object_id('[dbo].[fn_ResortDelimited]') is not null
     drop function [dbo].[fn_ResortDelimited]
go
create function [dbo].[fn_ResortDelimited](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns nvarchar(max)
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
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
		where
			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
end
/*
-- 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
as
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',' ')
*/
GO
 
select [dbo].[fn_ResortDelimited]('d,a,c',',')

Open in new window

0
 
acptAuthor Commented:
Excellent... A +++

worked exactly as I wanted it to be..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.