CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Nitin
Nitin

Reputation: 11

Sort varchar datatype with numeric characters

SQL SERVER 2005 SQL Sorting :

Datatype varchar

Should sort by

1.aaaa
5.xx
11.bbbbbb
12
15.

how can i get this sorting order

Wrong

1.aaaa
11.bbbbbb
12
15.
5.xx

Upvotes: 1

Views: 145

Answers (3)

Rich
Rich

Reputation: 3821

You could do this by calculating a column based on what's on the left hand side of the period('.').

However this method will be very difficult to make robust enough to use in a production system, unless you can make a lot of assertions about the content of the strings.

Also handling strings without periods could cause some grief

with r as (
select '1.aaaa' as string
union select '5.xx'
union select '11.bbbbbb'
union select '12'
union select '15.' )

select *
from r
order by 
    CONVERT(int, left(r.string, case when ( CHARINDEX('.', r.string)-1 < 1) 
                                     then LEN(r.string) 
                                     else CHARINDEX('.', r.string)-1 end )), 
    r.string

Upvotes: 1

tvm
tvm

Reputation: 3459

On Oracle, this would work.

SELECT
  *
FROM
  table
ORDER BY
  to_number(regexp_substr(COLUMN,'^[0-9]+')),
  regexp_substr(column,'\..*');

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

If all the entries have this form, you could split them into two parts and sort be these, for example like this:

ORDER BY 
    CONVERT(INT, SUBSTRING(fieldname, 1, CHARINDEX('.', fieldname))), 
    SUBSTRING(fieldname, CHARINDEX('.', fieldname) + 1, LEN(fieldname))

This should do a numeric sort on the part before the . and an alphanumeric sort for the part after the ., but may need some tuning, as I haven't actually tried it.

Another way (and faster) might be to create computed columns that contain the part before the . and after the . and sort by them.

A third way (if you can't create computed columns) could be to create a view over the table that has two additional columns with the respective parts of the field and then do the select on that view.

Upvotes: 0

Related Questions