How To Convert String to Numeric for Sorting?

[-cicada-]

I have a table of auction lots, where the lot number (Lot_No) is a
text (nvarchar) field. The reason that an nvarchar field has been
selected, is although most lot numbers are numeric, occasionally
auctioneers add in extra lots consisting of a numeric lot number,
followed by a letter, so an auction may have lots 101, 101, 101A, 101B
102, 103 etc. Being a text field, by default the Lot_No field sorts in
ascending order like this:
1
11
12
13
13A
....
2
20
21

I want to sort field Lot_No into true ascending numeric order.

If I use the SQL Convert function, it throws an error when it comes
across a Lot_No with an alpha character.

Any suggestions?

Thanks in advance for your assistance.

[Plamen Ratchev]

Here are two methods. The first one assumes you can have at most one
character to the right on some lot numbers. The second will clean all
non-numeric characters and then order. In the second solution replace the
subquery reference to master..spt_values with real utility table with
numbers (http://www.projectdmx.com/tsql/tblnumbers.aspx).

CREATE TABLE Lots (
lot_no NVARCHAR(5) NOT NULL PRIMARY KEY);

INSERT INTO Lots VALUES ('1');
INSERT INTO Lots VALUES ('11');
INSERT INTO Lots VALUES ('12');
INSERT INTO Lots VALUES ('13');
INSERT INTO Lots VALUES ('13A');
INSERT INTO Lots VALUES ('2');
INSERT INTO Lots VALUES ('20');
INSERT INTO Lots VALUES ('21');
INSERT INTO Lots VALUES ('101');
INSERT INTO Lots VALUES ('101A');
INSERT INTO Lots VALUES ('101B');
INSERT INTO Lots VALUES ('102');
INSERT INTO Lots VALUES ('103');

-- SQL Server 2000/2005
SELECT lot_no
FROM Lots
ORDER BY CASE WHEN RIGHT(lot_no, 1) LIKE '[A-Z]'
THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
ELSE CAST(lot_no AS INT)
END, lot_no

-- SQL Server 2005 only
;WITH CleanLots (lot_no, num_lot_no)
AS
(SELECT lot_no,
CAST((SELECT SUBSTRING(lot_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(lot_no)
AND SUBSTRING(lot_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS INT)
FROM Lots)
SELECT lot_no
FROM CleanLots
ORDER BY num_lot_no, lot_no;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[-cicada-]

Plamen,

Thanks for the prompt response. this solution works fine when it is
included as part of the SELECT statement:

SELECT *
FROM tblData

ORDER BY CASE WHEN RIGHT(lot_no, 1) LIKE '[A-Z]'
THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
ELSE CAST(lot_no AS INT)
END, lot_no

However on the page I allow the user to select the sort order, with
the default sort order being by Lot_No, as defined here:

<CFPARAM name="DefOrder" default= Lot_No>

However if I change the above line to:

<CFPARAM name="DefOrder" default= CASE WHEN RIGHT(lot_no, 1) LIKE '[A-

Z]'
THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
ELSE CAST(lot_no AS INT)
END, lot_no>

I receive an error message, as a result of inclusion of the brackets
in the expression.

Thanks again

John

[Plamen Ratchev]

I am really not sure what mechanism is used on your page to pass down the
ORDER BY to the query. A few approaches that may work:

1). Create a computed column in your table the has the CASE expression as
the definition. Then set the computed column name as default order column in
your page.

ALTER TABLE Lots
ADD num_lot_no AS (CASE WHEN RIGHT(lot_no, 1) LIKE '[A-Z]'

THEN CAST(LEFT(lot_no, LEN(lot_no) - 1)
AS INT)
ELSE CAST(lot_no AS INT)

END)

2). Create a view that has the column definition, then use the view on your
page.

CREATE VIEW CleanLots (lot_no, num_lot_no)
AS
SELECT lot_no,

CASE WHEN RIGHT(lot_no, 1) LIKE '[A-Z]'
THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
ELSE CAST(lot_no AS INT)
END

FROM Lots

3). Use a derived table to define the column in the query, then hopefully
your mechanism for passing the ORDER BY will apply it over the derived
table.

SELECT lot_no
FROM (SELECT lot_no,

CASE WHEN RIGHT(lot_no, 1) LIKE '[A-Z]'
THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
ELSE CAST(lot_no AS INT)
END

FROM Lots) AS L(lot_no, num_lot_no)

[Erland Sommarskog]

-cicada- (johnf...@a1.com.au) writes:
><CFPARAM name="DefOrder" default= Lot_No>
>
> However if I change the above line to:
>
><CFPARAM name="DefOrder" default= CASE WHEN RIGHT(lot_no, 1) LIKE '[A-
> Z]'
> THEN CAST(LEFT(lot_no, LEN(lot_no) - 1) AS INT)
> ELSE CAST(lot_no AS INT)
> END, lot_no>
>
> I receive an error message, as a result of inclusion of the brackets
> in the expression.

What about actually posting the error message? Wether you actually
do something like the above, I don't know, but I don't know ASP or
ASP .Net.

But wouldn't you at least have to put the entire SQL-expression in
double quotes? You may also have to replace [] with tokens starting
with &. On the top of my head, I don't know what the names for the
brackets are in HTML.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tom van Stiphout]

On Mon, 24 Mar 2008 16:01:06 +0000 (UTC), Erland Sommarskog
<esq...@sommarskog.se> wrote:

Sorry - CFPARAM suggests Cold Fusion, not ASP*.
The OP might want to continue this discussion in a CF group.
-Tom.

[Erland Sommarskog]

Tom van Stiphout (no.spam...@cox.net) writes:
> On Mon, 24 Mar 2008 16:01:06 +0000 (UTC), Erland Sommarskog
><esq...@sommarskog.se> wrote:
>
> Sorry - CFPARAM suggests Cold Fusion, not ASP*.
> The OP might want to continue this discussion in a CF group.
> -Tom.

Well, I don't know Cold Fusion either, as you can tell. :-)

Yes, if my uninitated guesses about SGML in general do not help, John
should find a forum for his web environment, whatever that may be.