ORDER BY PROBLEM

DECLARE @NumberTable TABLE( ItemNumber VARCHAR(50))

INSERT @NumberTable
SELECT 'ABC-1702-XYZ' ItemNumber UNION ALL
SELECT 'ABC-1727-XYZ' UNION ALL
SELECT 'ABC-1729-XYZ' UNION ALL
SELECT 'ABC-895-XYZ' UNION ALL
SELECT 'ABC-2119-XYZ' UNION ALL
SELECT 'ABC-1746-XYZ' UNION ALL
SELECT 'ABC-900-XYZ' UNION ALL
SELECT 'ABC-2215-XYZ' UNION ALL
SELECT 'ABC-645-XYZ' UNION ALL
SELECT 'ABC-1783-XYZ' UNION ALL
SELECT 'ABC-2193-XYZ' UNION ALL
SELECT 'ABC-830-XYZ' UNION ALL
SELECT 'ABC-646-XYZ' UNION ALL
SELECT 'ABC-796-XYZ'

select * from @NumberTable
order by ItemNumber ASC

SELECT
ItemNumber
FROM @NumberTable
ORDER BY CAST(SUBSTRING(SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1) + 1,LEN(ItemNumber)),1,CHARINDEX('-',SUBSTRING(ItemNumber,CHARINDEX('-',ItemNumber,1)+1,LEN(ItemNumber)),1)-1) AS INT)

DECLARE @NumberTable TABLE( ItemNumber VARCHAR(50))

INSERT @NumberTable
SELECT 'ABC-1702-XYZ' ItemNumber UNION ALL
SELECT 'ABC-1727-XYZ' UNION ALL
SELECT 'ABC-1729-XYZ' UNION ALL
SELECT 'ABC-895-XYZ' UNION ALL
SELECT 'ABC-2119-XYZ' UNION ALL
SELECT 'ABC-1746-XYZqqqq' UNION ALL
SELECT 'ABC-900-dfdfdfXYZ' UNION ALL
SELECT 'ABCasasa-2215-XYZabc' UNION ALL
SELECT 'ABC-64533333-XYZ' UNION ALL
SELECT 'ABC121-1783-333XYZ' UNION ALL
SELECT 'ABC-2193-XYZ' UNION ALL
SELECT 'ABC-830-XYZ' UNION ALL
SELECT 'ABC-646-XYZ' UNION ALL
SELECT 'ABC-796-XYZ'

select *
from @NumberTable
order by cast (SUBSTRING(
ItemNumber,
CHARINDEX('-', ItemNumber) + 1,
LEN(ItemNumber) - CHARINDEX('-', ItemNumber) - CHARINDEX('-', REVERSE(ItemNumber))) as int)

CELKO (9/1/2012)

>> I have column in a table which needs to be ordered in an ascending order. <<

Somebody did not do his homework! Tables have no ordering BY DEFINITION. If you want to have such a relationship in the table, you need to add a column for it; this is called “The Information Principle” and it is one of Dr. Codd's 12 rules. If you want to display data in a sorted order outside the database, then use the ORDER BY clause to create an implicit cursor. Is that what you meant?

When you have read that book on RDBMS, then read a book on SQL, so your will know the ANSI Standard syntax for insertion.

INSERT INTO Items
VALUES ('ABC-1702-XYZ'), ('ABC-1727-XYZ'),
('ABC-1729-XYZ'), ('ABC-895-XYZ'), ('ABC-2119-XYZ'),
('ABC-1746-XYZ'), ('ABC-900-XYZ'), ('ABC-2215-XYZ'),
('ABC-645-XYZ'), ('ABC-1783-XYZ'), ('ABC-2193-XYZ'),
('ABC-830-XYZ'), ('ABC-646-XYZ'), ('ABC-796-XYZ');

One answer is this. It is long but fast.

SELECT item_code
FROM (SELECT item_code,
CAST (REPLACE (
REPLACE (REPLACE (REPLACE (
REPLACE (REPLACE (REPLACE (item_code, 'A', ''),
'B', ''),
'C', ''),
'X', ''),
'Y', ''),
'Z', ''),
'-', '') AS INTEGER) AS sort_numeric_value
FROM Items)
ORDER BY sort_numeric_value;