Posted by Plamen Ratchev on 09/30/06 12:00

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

[Back to original message]