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 1 of 212 > >>
Author Messages
nathankatcgfdotorg
New Member
New Member

--
13 Dec 2007 07:30 AM
Hi. I have the following data that orders asc like so:

SSC 113
SSC 201
SSC 461
SSC 4610
SSC 462

I'd like to get the data to sort like this:

SSC 113
SSC 201
SSC 461
SSC 462
SSC 4610

IOW, to sort as if I had the SSC in one column and the numbers in another.
But data can also unpredictably be in other formats:

112
113
MM20BC
SSCPZ202C
Etc.

Is there an easy way to do this?
nathankatcgfdotorg
New Member
New Member

--
13 Dec 2007 10:24 AM
It needs to be sorted on more than numerical value alone.

If I said sort the following to you by hand without using a computer you could do it.

This is how I would expect them to sort if done by hand.

112
113
MM20BC
SSC 113
SSC 201
SSC 461
SSC 462
SSC 4610
SSCPZ202C


SQLUSA
New Member
New Member

--
13 Dec 2007 11:04 AM
Check this out: http://www.sqlusa.com/bestpractices2005/caseinorderby/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...inorderby/



Let us know if it works for you.



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

--
13 Dec 2007 12:35 PM
Unfortunately it didn't work for me.

Sorts:

SSC 461
SSC 4610
SSC 462

Desired is:

SSC 461
SSC 462
SSC 4610
nathankatcgfdotorg
New Member
New Member

--
13 Dec 2007 01:00 PM
Apparently the sort I desire is called natural.

It's how most non programmers would sort.

It's how the phone book is sorted (thanks Glen!).


http://www.codinghorror.com/blog/ar...01018.html
SQLUSA
New Member
New Member

--
13 Dec 2007 01:02 PM
SSC 461
SSC 462
SSC 4610

You have to create substring for the numeric tail and convert to money to get a sort like this.

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

nathankatcgfdotorg
New Member
New Member

--
13 Dec 2007 03:09 PM
Will that work on a larger set like the one mentioned earlier?

112
113
MM20BC
SSC 113
SSC 201
SSC 461
SSC 462
SSC 4610
SSCPZ202C
SQLUSA
New Member
New Member

--
13 Dec 2007 04:23 PM
Yes, that would work.

You have to isolate the digits and convert them to money.

First you have to put down all the patterns you will face.
SQLUSA
New Member
New Member

--
14 Dec 2007 05:48 AM
Great! Appears to be the perfect solution....
nathankatcgfdotorg
New Member
New Member

--
14 Dec 2007 08:45 AM
I don't think you guys get it. You are not alone... common to programmers.

Do this:

Create a folder named "sort" on your desktop in Windows. No quotes.
Throw the following folders into that folder.

112
113
MM20BC
SSC401
SSC 113
SSC 201
SSC 461
SSC 462
SSC 4610
SSCPZ202C

Click on the column header Name to sort the folders.

This is a natural dictionary sort, just like the phone book. It is how humans intuitively sort. It's the sort I'm looking for and one that frankly, should be offered in an ORDER BY clause in T-SQL.
SQLUSA
New Member
New Member

--
14 Dec 2007 10:03 AM
This is codepage 1256:


112
113
MM20BC
SSC 113
SSC 201
SSC 461
SSC 4610
SSC 462
SSCPZ202C


What is wrong with it?
SQLUSA
New Member
New Member

--
14 Dec 2007 10:05 AM
Actually, i see what is wrong with it


The 4610 should come after 462

Well...close but no cigar.....
nathankatcgfdotorg
New Member
New Member

--
14 Dec 2007 11:46 AM
So I'm looking at ways to do it on the fly (without creating any tables). THe max width of the column is 150 so it isn't too large. Max number of records to be sorted is about 10K per query. Avg is 1K.

So I'm looking at

SELECT PATINDEX('%[A-z]%','SSC461SSC4610')
SELECT PATINDEX('%[0-9]%','SSC461SSC4610')

that or CHARINDEX should do it. If I can get it down to work without a loop that would be sweet too.

Of course, if I take too long I'll abandon it and if it is too slow to run I'll abandon the code.

Thanks guys.
SwePeso
New Member
New Member

--
14 Dec 2007 01:07 PM
Anyone tried this approach?

SELECT Col1
FROM Table1
ORDER BY LEN(Col1), Col1
nathankatcgfdotorg
New Member
New Member

--
14 Dec 2007 01:53 PM
Won't work if the data is the following right?:

112
113
MM20BC
SSC 113
SSC 201
SSC 461
SSC 4610
SSC 462
SSCPZ202C
Z1
Z100
ZZ
SQLUSA
New Member
New Member

--
16 Dec 2007 12:44 AM
To put 4610 behind 462 in ASCENDING order requires programming logic.

Collations are character based. Even 1000000 should go beyond 462!

You have to isolate the number within the string and sort numeric on it.


Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com/ssis/



SwePeso
New Member
New Member

--
17 Dec 2007 03:03 AM
DECLARE @Sample TABLE (Info VARCHAR(200))

INSERT @Sample
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)
SQLUSA
New Member
New Member

--
17 Dec 2007 07:34 AM
Nice!

Not sure if this is a requirement:

SELECT 'SSC 461.0' UNION ALL
nathankatcgfdotorg
New Member
New Member

--
18 Dec 2007 01:32 PM
Peter, I haven't checked it yet, been very busy. If you solved the "how", you rock. Thanks!
nathankatcgfdotorg
New Member
New Member

--
18 Dec 2007 04:16 PM
Peter, you are great!!
You are not authorized to post a reply.
Page 1 of 212 > >>


Acceptable Use Policy