Alpha Numeric Mixed Order

Last Post 14 Dec 2007 01:07 PM by SwePeso. 28 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 2 of 2 << < 12
Author Messages
SQLUSA
New Member
New Member

--
18 Dec 2007 08:34 PM
Well, this maybe natural sort, but the algorithm to do it is certainly complex.
SwePeso
New Member
New Member

--
18 Dec 2007 10:39 PM
If the suggestion works, what is the problem?

For a another simple solution to a complex problem, see here
http://www.sqlteam.com/forums/topic...C_ID=93911

SQLUSA
New Member
New Member

--
18 Dec 2007 11:11 PM
Greate job Peter for the test case. But cant' possibly be the "natural sort" solution. Try this to see something unnatural:

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 (
SELECT Info,
CASE PATINDEX('%[0-9]%', Info)
WHEN 0 THEN LEN(Info)
WHEN 1 THEN 0
ELSE PATINDEX('%[0-9]%', Info) - 1
END AS Yak,
LEN(Info) AS e
FROM @Sample
) AS p
ORDER BY LEFT(Info, Yak),
e - Yak,
SUBSTRING(Info, Yak + 1, 200)




Kalman Toth
http://www.sqlusa.com/ssis/


SwePeso
New Member
New Member

--
19 Dec 2007 12:45 AM
Here you go!

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)
SwePeso
New Member
New Member

--
19 Dec 2007 12:47 AM
And the function code here


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
SQLUSA
New Member
New Member

--
19 Dec 2007 03:01 AM
Nice Peter! I like it.

Interesting study in sorting....beats the IBM sorting machine.... http://www.columbia.edu/acis/histor...orter.html


Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam
nathankatcgfdotorg
New Member
New Member

--
19 Dec 2007 09:26 AM
Yep, further testing today revealed the nice short code failed on these:

MS 800.004
MS 800.291
MS 800.293
MS 800.299
MS 800.72(g)
MS 800.111(g)
MS 800.112(g)
MS 800.113(g)
MS 800.114(g)

I'm going to test the new longer code and see how fast it runs. Looks good, of course not as amazingly efficient looking as the first. :-)

Now a little philosophical quetion, don't any of you feel this should be a built in code sort option in SQL, like ORDER BY [name] ASCNATURAL?

Heck, I do.

Thanks guys.
nathankatcgfdotorg
New Member
New Member

--
19 Dec 2007 09:29 AM
Hmmm, appears to choke.
SQLUSA
New Member
New Member

--
19 Dec 2007 10:52 PM
What is your volume?

Should be in SQL? If there is a demand for it. Actually, it should be in Windows first....


Functions are generally not very fast.

If this is really the NATURAL SORT you are looking for, it need to be optimized....probably an inline sped-up version....


Kalman Toth, Business Intelligence Architect
SQL Server 2005 Training - http://www.sqlusa.com/highperformance2005/

You are not authorized to post a reply.
Page 2 of 2 << < 12


Acceptable Use Policy