asked on

sql alphanumeric order

I have a table that stores page in alphanumeric manner. I need to be able order the page. The issue is they are in alphanumeric

The sample data is here

E-1.html
E-10.html
E-11.html
E-12.html
E-13.html
E-14.html
E-15.html
E-16.html
E-17.html
E-18.html
E-19.html
E-2.html
E-20.html
E-21.html
E-22.html
E-23.html
E-24.html
E-25.html
E-26.html
E-27.html
E-28.html
E-29.html
E-3.html
E-30.html
E-31.html
E-32.html
E-33.html
E-34.html
E-35.html
E-36.html
E-37.html
E-38.html
E-39.html
E-3a.html
E-4.html
E-40.html
E-41.html
E-42.html
E-43.html
E-44.html
E-45.html
E-46.html
E-47.html
E-48.html
E-49.html
E-5.html
E-50.html
E-6.html
E-7.html
E-8.html
E-9.html

Open in new window

sample query is here

SELECT *
		FROM dbTempType
		WHERE name LIKE 'E-%'
		ORDER BY 
CASE 
  WHEN ISNUMERIC(name)=1 
  THEN CAST(name as int)

  WHEN PATINDEX('%[^0-9]%',name) > 1 
  THEN CAST(
    LEFT(
      name,
      PATINDEX('%[^0-9]%',name) - 1
    ) as int)

  ELSE 2147483648
END, 


CASE 
  WHEN ISNUMERIC(name)=1 
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',name) > 1 
  THEN SUBSTRING(
      name,
      PATINDEX('%[^0-9]%',name) ,
      50
    ) 

  ELSE name
END

Open in new window

erikTsomik

ASKER

I have updated the code to do this and its almost there , but still not . E-3a.html is out of order , E-5 is out of order and so on

SELECT REVERSE(SUBSTRING(REVERSE(name), 
                       CHARINDEX('.', REVERSE(name)) + 1, 999)) as name
		FROM dbTempType
		WHERE name LIKE 'E-%'
		ORDER BY LEFT(name, PATINDEX('%[0-9]%', name)-1),
  CONVERT(INT,SUBSTRING(name,PATINDEX('%[0-9]%',REVERSE(SUBSTRING(REVERSE(name), 
                       CHARINDEX('.', REVERSE(name)) + 1, 999)) ),LEN(REVERSE(SUBSTRING(REVERSE(name), 
                       CHARINDEX('.', REVERSE(name)) + 1, 999)) )-3))

Open in new window

The data is here

E-1.html
E-2.html
E-3.html
E-4.html
E-6.html
E-7.html
E-8.html
E-9.html
E-5.html
E-10.html
E-11.html
E-12.html
E-13.html
E-14.html
E-15.html
E-16.html
E-17.html
E-18.html
E-19.html
E-20.html
E-21.html
E-22.html
E-23.html
E-24.html
E-25.html
E-26.html
E-27.html
E-28.html
E-29.html
E-30.html
E-31.html
E-32.html
E-33.html
E-34.html
E-35.html
E-36.html
E-37.html
E-38.html
E-39.html
E-3a.html
E-40.html
E-41.html
E-42.html
E-43.html
E-44.html
E-45.html
E-46.html
E-47.html
E-48.html
E-49.html
E-50.html

Open in new window

mankowitz

select filename from dbTempType
order by left(filename, 2), parse (substring(filename, patindex('%[0-9]%', filename), patindex('%[^0-9]%', substring(filename, 1+patindex('%[0-9]%',filename), 999))) as int)

mankowitz

select name, left(name, patindex('%[0-9]%', name)-1),
substring(name, patindex('%[0-9]%', name), patindex('%[^0-9]%', substring(name, 1+patindex('%[0-9]%',name), 999))) ,
substring(name, 1+len(substring(reverse(name), patindex('%[0-9]%',reverse(name)), 999)), 999)
from dbtemptype

order by left(name, 2),
parse (substring(name, patindex('%[0-9]%', name), patindex('%[^0-9]%', substring(name, 1+patindex('%[0-9]%',name), 999))) as int),
substring(name, 1+len(substring(reverse(name), patindex('%[0-9]%',reverse(name)), 999)), 999)

erikTsomik

ASKER

I am getting the parse is not recognized built-in function name

mankowitz

that's strange. Parse is valid tsql: https://msdn.microsoft.com/en-us/library/hh213316.aspx

try replacing it with the word cast.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_7965317

if your data is exact format use below query

select convert(int,replace(replace (column,'E-',''),'.html','')) from Table name

PortletPaul

CONVERT() or CAST() can cause failure is the data isn't suitable.

It would be better in my view to use TRY_CONVERT() or TRY_CAST() that are only available in recent versions of SQL Server and at least those will not fail if the data cannot be converted. However they return NULL if they cannot convert which would affect the sorting.

The reason I proposed using concatenation like this:
right('0000000000' + replace(replace([name],'E-',''),'.html',''),10)
is because it avoids conversion failures

Scott Pletcher

I propose breaking it into parts and sorting on the parts. Otherwise I don't think you'll end up with the sort order you want. I added the bolded data to point out potential sort issues later.

I used CROSS APPLYs to assign alias names to avoid repeating computations and to make the code easier to follow (at least to me).

SELECT page --*
FROM (VALUES
('E-1.html'),('E-10.html'),('E-11.html'),('E-12.html'),('E-13.html'),
('E-14.html'),('E-15.html'),('E-16.html'),('E-17.html'),('E-18.html'),
('E-19.html'),('E-2.html'),('E-20.html'),('E-21.html'),('E-22.html'),
('E-23.html'),('E-24.html'),('E-25.html'),('E-26.html'),('E-27.html'),
('E-28.html'),('E-29.html'),('E-3.html'),('E-30.html'),('E-31.html'),
('E-32.html'),('E-33.html'),('E-34.html'),('E-35.html'),('E-36.html'),
('E-37.html'),('E-38.html'),('E-39.html'),('E-3a.html'),('E-4.html'),
('E-40.html'),('E-41.html'),('E-42.html'),('E-43.html'),('E-44.html'),
('E-45.html'),('E-46.html'),('E-47.html'),('E-48.html'),('E-49.html'),
('E-5.html'),('E-50.html'),('E-6.html'),('E-7.html'),('E-8.html'),
('E-9.html'),
('E-3ac.html'),('E-3z.html'),
('E-3') /*I deliberately removed the "." from the data to demonstrate how it sorts without it */
) pages(page)
CROSS APPLY (
SELECT CHARINDEX('.', REVERSE(page)) AS suffix_length
) AS ca1
CROSS APPLY (
SELECT LEFT(page, LEN(page) - suffix_length) AS page_without_suffix
) AS ca2
CROSS APPLY (
SELECT PATINDEX('%[0-9]%', page_without_suffix) AS first_digit,
LEN(page_without_suffix) - PATINDEX('%[0-9]%', REVERSE(page_without_suffix)) + 1 AS last_digit
) AS ca3
ORDER BY
CASE WHEN first_digit = 0 THEN page_without_suffix
ELSE LEFT(page_without_suffix, first_digit - 1) END,
CASE WHEN first_digit = 0 THEN 0
ELSE CAST(SUBSTRING(page_without_suffix, first_digit, last_digit - first_digit + 1) AS int) END,
CASE WHEN first_digit = 0 THEN ''
ELSE SUBSTRING(page_without_suffix, last_digit + 1, 8000) END,
RIGHT(page, suffix_length)