CODEWITHSUNDEEP
mysql
Des Hutch
Des Hutch

Reputation: 293

Sort string containing text and numbers

I'm trying to query and sort a column without much luck. My data looks like this and is consistent with the preceding "WP".

Should display as

  WP1-WP2
    WP2-WP3
    WP10-WP11
    WP10-WP12

Actually displays as

 WP1-WP2
    WP10-WP11
    WP10-WP12
    WP2-WP3

I've attempted adapting several answers found on here with no success.

Question is how do I sort this list as it should??

EDIT: I am using distinct in the query, not sure if this will effect the problem I'm having

Upvotes: 2

Views: 216

Answers (2)

AK47
AK47

Reputation: 3807

Try this,

 select *
 from myStringSorting
order by Cast(Replace(Substring(mycol,0,CHARINDEX('-',mycol)),'WP','') as Int) asc
        ,Cast(Replace(Substring(mycol,CHARINDEX('-',mycol) + 1,LEN(mycol)),'WP','') as Int) asc

Check out this link, http://sqlfiddle.com/#!3/ca45f/5

With DISTINCT,

With CTE as
(
select distinct mycol
 from myStringSorting
 )
 select * from CTE
order by Cast(Replace(Substring(mycol,0,CHARINDEX('-',mycol)),'WP','') as Int) asc
        ,Cast(Replace(Substring(mycol,CHARINDEX('-',mycol) + 1,LEN(mycol)),'WP','') as Int) asc

With DISTINCT, without CTE, using sub query,

Select a.mycol from
(
select distinct mycol
 from myStringSorting
 )as a
order by Cast(Replace(Substring(a.mycol,0,CHARINDEX('-',a.mycol)),'WP','') as Int) asc
        ,Cast(Replace(Substring(a.mycol,CHARINDEX('-',a.mycol) + 1,LEN(a.mycol)),'WP','') as Int) asc

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Another method

select * from myStringSorting
order by len(mycol),mycol

Upvotes: 1

Related Questions