Sort a concatenated string in sql server 2005

Posted on 2008-11-04
Last Modified: 2012-05-05

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


Question by:acpt
    LVL 39

    Accepted Solution

    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


    Author Closing Comment

    Excellent... A +++

    worked exactly as I wanted it to be..

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now