--**************************************
--     
-- Name: Sorting a String using T-SQL
-- Description:This is a User Defined Fu
--     nction in T-SQL for MSSQL Sevre 2000 whi
--     ch sorts the input string, and returns i
--     t to the caller. Optinals include sort d
--     irection (ascending/descending), sort pr
--     eference(lower case / upper case prefere
--     nce), sort type (case sensitive / insens
--     itive).
-- By: Gaurav Pugalia
--
-- Inputs:Parameters: 4				
@inStr : input string TO be sorted,
@sortDirection : Ascending or Descending
@sortType: CASE Insensitive or sensitive 
@preference : Lower OR Upper CASE preference.
--
-- Returns:Sorted string as per specifie
--     d preferences
--
-- Assumes:Transact-SQL
--
-- Side Effects:Tell me about 'em !
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=568&lngWId;=5--for details.--**************************************
--     

CREATE FUNCTION funcStrSort (@inStr varchar(8000), @sortDirection char,
 @sortType char, @preference char)
RETURNS varchar(8000)
/************************* funcStrSort **********************************************************************
Written By: Gaurav Pugalia																				*
Date : October 07, 2002																			*
Description	 : This UDF sorts the input string 															*
Compatibility : MSSQL Server 2000																			*
																											*
Parameters : 4																							*
@inStr : input string TO be sorted, upto 8000 characters											*
 (since MSSQL varchar fields are limited TO 8000 characters)								*
@sortDirection : Ascending(default) or Descending(use 'd' or 'D' TO specify descending sort)				*
@sortType : CASE Insensitive(default) or Sensitive(use 'S' or 's' TO specify a sensitive sort)			*
@preference: Lower OR Upper CASE preference. DEFAULT IS the collating sequence (assuming ASCII). 		*
				 Specify a 'l' OR 'L' TO specify lower CASE preference), ignored if insensitive search *
																											*
RETURN Value: Sorted string AS per specified preferences													*
																											*
*************************************************************************************************************/
AS
    BEGIN -- begin function
    DECLARE @len smallint, @i smallint, @j smallint
    DECLARE @char1 char, @char2 char
    SET @len = LEN(@inStr)
    SET @i = 1
    WHILE @i <= @len-1
        BEGIN-- begin major loop
        	SET @j = @i + 1
        	WHILE @j <= @len
        	BEGIN			-- begin minor loop
        		SET @char1 = SUBSTRING(@inStr,@i,1)
        		SET @char2 = SUBSTRING(@inStr,@j,1)
        		IF @sortType IS NULL OR @sortType = '' OR UPPER(@sortType) <> 'S'
        		BEGIN
        			IF @char2 < @char1
        			BEGIN
        	 		 SET @inStr =
        LEFT(@inStr,@i-1) + @char2 + SUBSTRING(@inStr,@i+1,@j-@i-1) + @char1 + RIGHT(@inStr,@len-@j)
        			END
        		END
        		ELSE
        		BEGIN
        			IF ASCII(@char2) < ASCII(@char1)
        			BEGIN
        	 		 SET @inStr =
        LEFT(@inStr,@i-1) + @char2 + SUBSTRING(@inStr,@i+1,@j-@i-1) + @char1 + RIGHT(@inStr,@len-@j)
        			END
        		END
        	SET @j = @j + 1 -- increment minor loop counter
        	END				-- end minor loop
        SET @i = @i + 1 -- increment major loop counter
    END				-- end major loop

    /* IF sensitive sort AND lower CASE prefernce, do the needful */
    IF UPPER(@sortType) = 'S' AND UPPER(@preference) = 'L'
        BEGIN
        	SET @i = @len
        	WHILE @i > 0 AND ( ASCII(SUBSTRING(@inStr,@i,1)) BETWEEN ASCII('a') AND ASCII('z') )
        	BEGIN
        		SET @i = @i-1
        	END
        	SET @inStr = RIGHT(@inStr,@len-@i) + LEFT(@inStr,@i)
    END

    /* IF want a descending sort, reverse the string */
    IF UPPER(@sortDirection) = 'D'
        BEGIN
        	SET @inStr = REVERSE(@inStr)
    END

    RETURN @inStr-- return the sorted string
END -- end function

GO