?
Solved

Sort a concatenated string in sql server 2005

Posted on 2008-11-04
2
Medium Priority
?
403 Views
Last Modified: 2012-05-05
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..

0
Comment
Question by:acpt
2 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 22879008
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
 

Author Closing Comment

by:acpt
ID: 31513216
Excellent... A +++

worked exactly as I wanted it to be..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question