Algorithm to sort strings mixed with Alpha and Numeric values.

Wed Dec 19, 2007 by Peter Larsson in sql-server-2008, algorithms, sql-server-2005, sql-server-2000

CREATE FUNCTION dbo.fnSplitType
(
        @Data VARCHAR(200),
        @PartSize TINYINT
)
RETURNS VARCHAR(8000)
AS
BEGIN
        DECLARE @Result VARCHAR(8000),
                @Alpha TINYINT,
                @OldPosition SMALLINT,
                @NewPosition SMALLINT

        SELECT  @Result = '',
                @Alpha = 1,
                @OldPosition = 1,
                @NewPosition = 1

        IF @Data LIKE '[0-9]%'
                SELECT @Result = REPLICATE(' ', @PartSize),
                        @Alpha = 0

        WHILE @NewPosition < LEN(@Data)
                SELECT @NewPosition = CASE @Alpha
                                                WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))
                                                ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))
                                        END,
                        @NewPosition = CASE @NewPosition
                                                WHEN 0 THEN LEN(@Data)
                                                ELSE @OldPosition + @NewPosition - 2
                                        END,
                        @Result = @Result +     CASE @Alpha
                                                        WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)
                                                        ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)
                                                END,
                        @Alpha = 1 - @Alpha,
                        @OldPosition = @NewPosition + 1

        RETURN  RTRIM(@Result)
END

Here is the code to test with

DECLARE    @Sample TABLE (Info VARCHAR(200))

INSERT     @Sample
SELECT     'S0C 4610' UNION ALL
SELECT     'S9C 113' UNION ALL
SELECT     'S1C 462' UNION ALL
SELECT     '112' UNION ALL
SELECT     '113' UNION ALL
SELECT     'MM20BC' UNION ALL
SELECT     'SSC 113' UNION ALL
SELECT     'SSC 201' UNION ALL
SELECT     'SSC 461' UNION ALL
SELECT     'SSC 4610' UNION ALL
SELECT     'SSC 462' UNION ALL
SELECT     'SSCPZ202C' UNION ALL
SELECT     'Z1' UNION ALL
SELECT     'Z100' UNION ALL
SELECT     'ZZ'

SELECT     Info
FROM       @Sample
ORDER BY   dbo.fnSplitType(Info, 8)

Legacy Comments


Jeff Smith 2007-12-19	re: Algorithm to sort strings mixed with Alpha and Numeric values. Usage advice: Don't ORDER BY this function, use a trigger or something to store the value returned by this function in an additional, indexed "Sort" column on your table(s).

Dewayne Christensen 2007-12-19	re: Algorithm to sort strings mixed with Alpha and Numeric values. Say it louder Jeff. I can't begin to count the number of times I've seen people use functions this way and absolutely slaughter performance.

Peso 2007-12-19	re: Algorithm to sort strings mixed with Alpha and Numeric values. The purpose is not to use the function to sort by ad-hoc. I showed a way to deal with the difficulties of this sort. I just sorted to show that the sort can be done and because the sample set is so small.

Jeff Smith 2007-12-19	re: Algorithm to sort strings mixed with Alpha and Numeric values. The "usage advice" wasn't really for Peso, it was for anyone who decides to use this function. One other thing, though, and this does happen a lot in the forum posts: there is just code here, and no clear, completely defined spec for what it does and the rules it is using to sort things. A generic "mixed alpha and numeric" sort can be defined and interpreted in many different ways, so I would suggest clearly indicating what this function does and how it handles different situations. Otherwise, people will complain about "bugs" in the code because everyone is making up their own spec.