SQL Query Order For Dropdown List

[iRadiate]

I have a dropdown list populated from a SQL Query: SELECT DISTINCT Wire FROM Cables ORDER BY Wire

It returns the character data like this:

W1
W11
W12
W109
W2
W20
W21
W3
... etc

I would like the data displayed (numerically increasing) like this instead:

W1
W2
W3
W11
W12
W20
W21
W109
... etc

Anyone know how to query the table to do this, I can't seem to figure this out

[jgrannis]

Re: SQL Query Order For Dropdown List

With MySQL, this should work:

SELECT DISTINCT Wire, CAST(Wire as SIGNED) AS casted_Wire FROM Cables ORDER BY casted_Wire ASC , Wire ASC

But I guess you only want the Wire column. So try:

SELECT Wire FROM (SELECT DISTINCT Wire, CAST(Wire as SIGNED) AS casted_Wire FROM Cables ORDER BY casted_Wire ASC , Wire ASC) AS temp

[iRadiate]

Re: SQL Query Order For Dropdown List

Thanks Jeff, but I'm using MS SQL and SIGNED is not a valid data type.
Even if I use a valid numeric data type such as DECIMAL it can't do the conversion from character to numeric. Which makes sense I guess, how could it know how to convert W1 to a numeric value.

[Steve Workings]

Re: SQL Query Order For Dropdown List

Maybe this? It strips out the first character "W"

select distinct convert(int, substring(wire,2,20)) as Wire from cables

Or is there more than just getting rid of the "W" ?

Edit: Do you need "distinct"? Maybe:

select Wire from cables order by convert(int, substring(wire,2,20))

Edit again, ignore all above. Try this ugly thing:

Select wire from Cables
order by convert(int,Left(SubString(Wire, PatIndex('%[0-9.-]%', Wire), 8000), PatIndex('%[^0-9.-]%', SubString(Wire, PatIndex('%[0-9.-]%', Wire), 8000) + 'X')-1))

[jgrannis]

Re: SQL Query Order For Dropdown List

Or this:

SELECT Wire FROM Cables
GROUP BY Wire
ORDER BY LEFT(Wire, PATINDEX('%[0-9]%', Wire)-1), CONVERT(INT, SUBSTRING(Wire, PATINDEX('%[0-9]%', Wire), LEN(Wire)))

Even for data that looks more like:
'W1','W11','W2','W109','A05','AA3','B05','A4','A6','A05'

[Steve Workings]

Re: SQL Query Order For Dropdown List

Jeff - my last suggestion was because I looked at the image in the original post - it includes "W4a" and "W4b", complicates matters a bit.

[jgrannis]

Re: SQL Query Order For Dropdown List

Oups. Yes, I missed those cases. Thanks

[iRadiate]

Re: SQL Query Order For Dropdown List

SELECT Wire FROM Cables
GROUP BY Wire
ORDER BY LEFT(Wire, PATINDEX('%[0-9]%', Wire)-1), CONVERT(INT, SUBSTRING(Wire, PATINDEX('%[0-9]%', Wire), LEN(Wire)))

Jeff, no unfortunately it still chokes on the W14a convert to integer.

Select wire from Cables
order by convert(int,Left(SubString(Wire, PatIndex('%[0-9.-]%', Wire), 8000), PatIndex('%[^0-9.-]%', SubString(Wire, PatIndex('%[0-9.-]%', Wire), 8000) + 'X')-1))

Steve, it works, that's awesome!!! See screenshot.

Thank you both very much, I really appreciate your help with this.

Stephen