Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Algorithm to sort strings mixed with Alpha and Numeric values.

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)

Print | posted on Wednesday, December 19, 2007 2:20 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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).
12/19/2007 4:54 PM | Jeff Smith
Gravatar

# 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.
12/19/2007 8:37 PM | Dewayne Christensen
Gravatar

# 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.
12/19/2007 8:48 PM | Peso
Gravatar

# 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.
12/19/2007 9:06 PM | Jeff Smith
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET