SQL Sort Problem

I am new to SQL and am trying to sort some information. The data
isbeing sorted, but not in the way I desire it to be.

Example: Sorted Table:
Product Name
-------------------------
Test-1
Test-10
Test-11
Test-12
Test-2
Test-3
Test-4

Desired Table:
Product Name
-------------------------
Test-1
Test-2
Test-3
Test-4
Test-10
Test-11
Test-12


Thanks for any help.

Re: SQL Sort Problem

Here is one way, but assumes existence of single '-' followed by a numeric
value:

CREATE TABLE Foo (
product_name VARCHAR(20))

INSERT INTO Foo VALUES ('Test-1')
INSERT INTO Foo VALUES ('Test-10')
INSERT INTO Foo VALUES ('Test-11')
INSERT INTO Foo VALUES ('Test-12')
INSERT INTO Foo VALUES ('Test-2')
INSERT INTO Foo VALUES ('Test-3')
INSERT INTO Foo VALUES ('Test-4')

SELECT product_name
FROM Foo
ORDER BY CAST(STUFF(product_name, 1, CHARINDEX('-', product_name), '') AS
INT)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: SQL Sort Problem

>> The data is being sorted, but not in the way I desire it to be. <<

You did not bother to tell us HOW you want it sorted or what the table
looks like. My guesses are:

(1) You want to first sort by some undefined alpha prefix that can be
upper and lowercase mixed (no rules for how to handle that were given)
and an integer postfix that should have been in its own column in the
table. Redesign the table and split this into two columns. You then
do an ORDER BY on both columns.

(2) The encoding is poorly designed and you need to add zeros in the
string, and validate it it in the DDL. Clean up the existing data,
then add a constraint to prevent a repetition of the problem,
something like this:

CREATE TABLE Products
(..
product_name CHAR(7) NOT NULL
CHECK (product_name LIKE '[A-Z'][a-z][a-z][a-z]-[0-9][0-9]'),
..);

In short, the problem is in the data and not in the procedural code.

Re: SQL Sort Problem

Assuming you can do this , split the column , and then order on the basis of
the Integer

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"SQLLearner" wrote in message
news:c396db7b-2138-4db5-bb7d-c6f3c0e0d41f@c23g2000hsa.google groups.com...
>I am new to SQL and am trying to sort some information. The data
> isbeing sorted, but not in the way I desire it to be.
>
> Example: Sorted Table:
> Product Name
> -------------------------
> Test-1
> Test-10
> Test-11
> Test-12
> Test-2
> Test-3
> Test-4
>
> Desired Table:
> Product Name
> -------------------------
> Test-1
> Test-2
> Test-3
> Test-4
> Test-10
> Test-11
> Test-12
>
>
> Thanks for any help.