creating a udf

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.
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,dHow about some DDL and sample data?
MLsql

Creating a simple update trigger

I am extremely new at SQL Server2000 and t-sql and I'm looking to
create a simple trigger. For explanation sake, let's say I have 3
columns in one table ... Col_1, Col_2 and Col_3. The data type for
Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
the same, set the value on Col_3 to "Completed". Can someone please
help me?

Thanks,
JustinHi

Posting DDL (Create table statements etc.) and example data (as Insert
statements) removes any ambiguity. Within the trigger try something like:

UPDATE t
SET Col_3 = "Completed"
FROM MyTable t JOIN INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.col2 <> d.col2
AND i.col2 = i.col1

John

"Justin" <jhosman@.numc.edu> wrote in message
news:450900ad.0407151958.73494eeb@.posting.google.c om...
> I am extremely new at SQL Server2000 and t-sql and I'm looking to
> create a simple trigger. For explanation sake, let's say I have 3
> columns in one table ... Col_1, Col_2 and Col_3. The data type for
> Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
> Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
> the same, set the value on Col_3 to "Completed". Can someone please
> help me?
> Thanks,
> Justin|||If col_3 is always based on the value of columns 1 and 2 then you don't need
it and you don't need the trigger either. Drop col_3 and calculate the
"completed" status in a view or when you query the table. Columns based on
other (non-key) columns are called Transitive Dependencies and avoiding them
is one of the goals of correct database design.

--
David Portas
SQL Server MVP
--|||David,

You're right, of course but I was just giving an example. What I
actually have is 19 columns. 18 of them are bit value 1 of them char.
The 18 represent 9 pairs. Each pair represents requested and created.
I wanted to write an update trigger on all 9 created fields that would
check to see if ALL of the requested fields were equal to thier
respective created fields and if they were, to Change the status of
field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
completed by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

If you have any insight in this area it would be much appreciated. I
certainly need all of the help I can get.

Thanks,

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi John,

Thanks for your help I really appreciate it. I tried your code and I
keep getting "invalid column name" error messages. Here is my attempt
at the code...

CREATE TRIGGER CHECKSTATUS
ON dbo.useraccounttbl
FOR UPDATE AS
IF UPDATE (internetcreated)
BEGIN
UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.internetcreated <> d.internetccreated
AND i.internetcreated = i.internetrequested
END

I'm tring to test this on these 3 columns what I will need to do on 16
more. I explained it to another person who reponded like this...

What I actually have is 19 columns. 18 of them are bit value 1 of them
char. The 18 represent 9 pairs. Each pair represents requested and
created. I wanted to write an update trigger on all 9 created fields
that would check to see if ALL of the requested fields were equal to
thier respective created fields and if they were, to Change the status
of field 19 from active to completed.

This is for a new user account form whereby a user would request access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. That way every time one
administrator created their respective account the database would check
and possible change the status of the request.

Thanks again for all of your help.

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

pkcol are the columns in the primary key, it could be more than one column
that uniquely defines each row. As there was/is no DDL it was used as short
hand.

You can pair each value

UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE ( i.col1 <> d.col1
AND i.col1 = i.col2 )
OR ( i.col3 <> d.col3
AND i.col3 = i.col4 )
OR ( .......
)

John

"Justin Hosman" <hateface72@.yahoo.com> wrote in message
news:40f7dbfb$1$16414$c397aba@.news.newsgroups.ws.. .
> Hi John,
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END
> I'm tring to test this on these 3 columns what I will need to do on 16
> more. I explained it to another person who reponded like this...
> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.
> This is for a new user account form whereby a user would request access
> to certain systems (administrated in diffrent areas) and the request
> status would stay in an active state until all of the accounts were
> created by the various administrators. That way every time one
> administrator created their respective account the database would check
> and possible change the status of the request.
> Thanks again for all of your help.
> Justin
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||>> Here is my attempt at the code... <<

I am sure that someone will kludge something really ugly and complex
for you, but the real problem is that you are still an assembly level
programmer trying to move to an abstract high level language.

1) Stop putting that silly "-tbl" prefix on table names! This is
saying that your table models pieces of furniture that belong to user
accounts, according to ISO-11179. There is only one data structure in
SQL anyway. What you want is a name that tells us what set of
entities or relationship the table models; since it is a set,
something like "UserAccounts" is fine.

Volumns are not anything like Fields; until you know and think in
terms of rows and columns, you will never "get it" and will continue
to write BASIC in SQL.

2) SQL is a declarative language and probably the first such animal
you have ever seen. You tell it WHAT you want and it figures out HOW
to get it. This is a huge advance in programming. If the data
changes, the query stays the same but the execution plan changes with
you coding anything new.

>> What I actually have is 19 columns. 18 of them are bit value 1 of
them
char. The 18 represent 9 pairs. Each pair represents requested and
created. <<

ARRGH! Assembly language and punch card programming!! Think
relational, not physical. At best from this description, you have
nine attributes which ought to be in one column each; having "half an
atribute" is a design flaw called attribute splitting. At this point,
I have to guess at specs, but it looks like each attribute has two
values (requested and created). NEVER, NEVER, NEVER use BIT data (I
have an article due out soon in DBAzine with some of the many
reasons).

Sit down and design a status code with all the values you need; you
have
"requested" and "created", so nothing is needed for "failed" or
"rejected" in your situation?

The right way to do this is NOT with a trigger (procedural code!! The
HOW of the answer), but with a VIEW that will compute the status
(declarative code!! The WHAT of the answer).

>> This is for a new user account form whereby a user would request
access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. <<

Now we have specs and can see that you don't know about First Normal
Form (1NF). The nine pairs are a repeating group in the data model.
Let us "flatten" out the table.

CREATE TABLE UserAccounts
(user_id INTEGER NOT NULL -- tables relate to each other, files don't
REFERENCES Personnel (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
admin_area INTEGER NOT NULL -- tables relate to each other, files
don't
REFERENCES Administration (admin_area)
ON DELETE CASCADE
ON UPDATE CASCADE,
request_status INTEGER DEFAULT 0 NOT NULL -- zero is "requested,not
granted"
CHECK (request_status IN (..)) -- columns have
constraints,fields don't
PRIMARY KEY (user_id, admin_area));

Your report:

CREATE VIEW RequestStatus (user_id, total_status)
AS
SELECT user_id, MIN(request_status)
FROM UserAccounts
GROUP BY user_id;

Add or drop admin areas as you wish; add more status codes easily; DRI
actions maintain the schema for you. The schema is a WHOLE, not a
collection of files that stand alone -- design the whole!|||Justin Hosman (hateface72@.yahoo.com) writes:
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END

"Completed" should be 'Completed'. SQL Server actually permits you
to use both " and ' to delimit strings, but " is only possible under
certain conditions, so stick with '.

> What I actually have is 19 columns. 18 of them are bit value 1 of them
> char. The 18 represent 9 pairs. Each pair represents requested and
> created. I wanted to write an update trigger on all 9 created fields
> that would check to see if ALL of the requested fields were equal to
> thier respective created fields and if they were, to Change the status
> of field 19 from active to completed.

A computed column would be easier:

CREATE TABLE blablabla
(pkcol ...
request1 bit NOT NULL,
completed1 bit NOT NULL,
..
status AS (CASE WHEN request1 = completed1 AND
request2 = completed2 AND
...
request9 = comepleted9 THEN 'Completed'
ELSE 'Pending'
END)

However, this is not a very good design. You should probaly change
all these columns to rows, so that if a tenth request is added, all
you need to add one more row to defining table:

CREATE TABLE requesttypes
(reqtype char(3) NOT NULL, -- mnemonic
reqname varchar(40) NOT NULL, -- descriptive
CONSTRIAINT PK_reqtypes PRIMARY KEY requesttypes (reqtype))

CREATE TABLE requests (
usrid int NOT NULL,
reqtype char(3) NOT NULL,
status char(1) NOT NULL
-- New or completed
CONSTRAINT ckc_req_status CHECK (status IN ('N', 'C')),
CONSTRAINT pk_reqs PRIMARY KEY (usrid, reqtype),
CONSTRAINT fk_regtype FOREIGN KEY (reqtype)
REFERENCES requesttypes( requtype),
CONSTRAINR fk_userd FOREIGN KEY (usrid)
REFERENCES users (usrid))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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