Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steve_scoggins
Starting Member
2 Posts |
 Posted - 2004-09-16 : 11:18:38
|
| I have imported a *.csv table into a SQL database.Now I want to sort a table with a text column with text strings like the following:1LINE2LINE...100LINE101LINE...When I create a query using the default ascending sort on this column I get an alphabetical ASCII sort and the numbers are in the wrong order.How can I create a query to sort the numeric portion of the strings using numerical sort order?It seems I need to write a SQL script to obtain the numeric portion of the string then use CAST or CONVERT to get the integer value and then do the sort. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-09-16 : 11:29:10
|
| ORDER BY CONVERT(INT, REPLACE(MyValue, 'LINE', ''))Mark |
 |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-16 : 13:35:27
|
| Or you can doorder by right(20,'00000000000000000000'+mycol)to just stick enough leading 0's on the left to make them all equal in length.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
steve_scoggins
Starting Member
2 Posts |
Posted - 2004-09-20 : 11:20:06
|
| Hello Ken and MarkThank you both for the helpful suggestions.I tried Ken's method and it worked great.order by right(20,'00000000000000000000'+mycol)Thank you very much, Steve |
|
|
|
|
|
|
Query Sort Strings with Numeric Values