Alpha-Numeric Collation

I have a column of data that needs to be sorted alpha-numerically.
Rules: I can not do this in a SQL Select statement or programming on the
middle-tier. It must be set as an index or some sort of collation property
of the collumn.
Data Example: This is the order it is returning.
PSW1
PSW2
PSW17
PSW3
I need it to return like this:
PSW1
PSW2
PSW3
PSW17
Is this possible with a certain type of index or collation setting?Firstly this is not having a go at you personally but this question occurs
time and time again so I will explain my experiences and solution.
Secondly it does not provide you with a complete solution. I'm sorry. (but
you may be able to write a function that returns an integer of the first
numeric and then sort on the substrings)
ORDER BY SUBSTRING([Key], 1, [dbo].fn_FirstNumeric([Key]) -1),
CONVERT(INT,SUBSTRING([Key], [dbo].fn_FirstNumeric([Key]), 999))
WHERE the function looks like
CREATE FUNCTION fn_FirstNumeric ( @.Data NVARCHAR(255) ) RETURNS INT
AS
BEGIN
RETURN PATINDEX('%[0-9]%', @.Data)
END
It may be that my explanation of your situation is not correct but it seems
that you have a key PSW17 which should be further sub-divided into
constituent keys.
While working for a very large semi-conductor manufacturing company I was
asked to perform exactly the same sort of collation/sorting on part numbers
such as
SNJ74LS145J
SNC72LP245N
SNB74S174N
the first 3 characters meant something specific so did the next 2 numbers,
the middle letters also, then the next digits something else and finally the
last character.
If I remember correctly it was (in that order)
Specification type/burn type (components were subjected to high temperaturs
and re-tested, those that still worked were sold to military)
Can't remember the next one
Another spec type L = low power, LS = low power Schottkey
Parent bar type (all 174 bars became either military, standard, encapsulated
in plastic or ceramic).
Package (plastic, ceramic)
You can imagine trying to get at data when asked
"I want all low power schottkey with parent bar 174 for non-military"
So we decided that at the point of data entry the part number would be split
up into into constituent parts and for display puproses the parts were
concatenated to provide the complete part number. We could then process the
data as requested as each part was in a seperate column.
So what's is the point of explaining this?
Well, SQL manages "Databases" the first part of this word is Data and if
this is not correct then you do not have a database but a base of data which
your will struggle with. You must correctly normalize and identify all
correct data parts, keys and sub-key for your system to work correctly.
Nik Marshall-Blank MCSD/MCDBA
"Eric Laechelin" <EricLaechelin@.discussions.microsoft.com> wrote in message
news:36414A3C-54F4-43FD-9633-F95918E47A84@.microsoft.com...
>I have a column of data that needs to be sorted alpha-numerically.
> Rules: I can not do this in a SQL Select statement or programming on the
> middle-tier. It must be set as an index or some sort of collation
> property
> of the collumn.
> Data Example: This is the order it is returning.
> PSW1
> PSW2
> PSW17
> PSW3
> I need it to return like this:
> PSW1
> PSW2
> PSW3
> PSW17
> Is this possible with a certain type of index or collation setting?