creating a function for natural alphanumeric sorting

I have built a query to parse the first 4 chunks of data in a string for
natural alphanumeric sorting, and would be extremely and eternally grateful
if someone could show me how to turn this into a function that I could use
like above. Here is the ddl and some sample data:
CREATE TABLE [tblSamples] ([FIELDID] [varchar] (50))
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102D-050215')
INSERT INTO tblsamples (fieldid) VALUES ('B104D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B105D-050525')
INSERT INTO tblsamples (fieldid) VALUES ('B201D-050523')
INSERT INTO tblsamples (fieldid) VALUES ('B401D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050523D')
INSERT INTO tblsamples (fieldid) VALUES ('B103DR-050213')
INSERT INTO tblsamples (fieldid) VALUES ('B101M-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102M-050215')
I would like to be able to feed in the name of the column to be sorted(it is
'fieldid' below) and the name of the database(it is 'tblsamples' below). I
am having trouble in figuring out how to implement/create a function which
would act like this
select * from tblsamples
order by naturalsort('fieldid', 'tblsamples')
Thanks a lot. Here is my query:
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )
AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd'
from tblsamples
order by a,b,c,d
create table tblsamples>> I would like to be able to feed in the name of the column to be sorted(it
Such generic sorting attempts seems to offer nothing beneficial and
introduce unwanted complexity. Write clean queries with simple ORDER BY
clauses. If you have to come up with extra-ordinary string parsing routines
for an ORDER BY clause, perhaps you should reconsider your schema design --
it is quite possible that during logical design, multiple values are jammed
into a single column, due to faulty analysis of the business model.
What exactly are you trying to do? Do you have similarly structured tables?
If so, what is the sorting logic? Your script is too complex to understand
what you are trying to accomplish here.
Anith|||I cannot redesign the scema and I have no control over the naming of the
values in the fieldid column. I work in Environmental Consulting, and all o
f
my data is geochemical and/or hydrogeological data from water and/or soil
sampling. Whatever the field person writes down for a fieldid on the chain
of custody MUST be the value in the database. So, usually I get a set of
wells like this:
mw-1
mw-10
mw-2
mw-3
but, I want a natural order on these sampling locations, i.e.
mw-1
mw-2
mw-3
mw-10
To further complicate things, sometimes they use a dash, sometimes they
dont, sometimes these use emw, not mw, etc. etc.
What I am trying to do is sort text string with numbers and characters the
way a human. So, I am parsing the string into chunks, text chunk, number
chunk, text chunk, number chunk, to achieve a natural alphanumeric order.
Thanks for the help.
Archer
"Anith Sen" wrote:

>
> Such generic sorting attempts seems to offer nothing beneficial and
> introduce unwanted complexity. Write clean queries with simple ORDER BY
> clauses. If you have to come up with extra-ordinary string parsing routine
s
> for an ORDER BY clause, perhaps you should reconsider your schema design -
-
> it is quite possible that during logical design, multiple values are jamme
d
> into a single column, due to faulty analysis of the business model.
> What exactly are you trying to do? Do you have similarly structured tables
?
> If so, what is the sorting logic? Your script is too complex to understand
> what you are trying to accomplish here.
> --
> Anith
>
>|||If the numeric portion is always at the end of the string, then you can use
the following expression to extract them.
CAST( RIGHT( c, PATINDEX( '%[^0-9]%', REVERSE( c ) ) - 1 ) AS INT )
Anith