14,735,205 members
See more:
Hai
I need to select data order by asc or descif values have alphabets,
for example
i have 2 fields,one is id(running increment ) and another one is voltage

like
ID Voltage
1 33Kv
2 11Kv
3 100Kv
4 30Kv
5 500kv

the above voltage i need to order by asc or desc if i use query like
`select * from DBTree  order by Voltage asc`

what i am getting is

```ID            Voltage
1              11Kv
2              100Kv
3              30Kv
4              33Kv
5              500kv```

but what i need to get

```ID            Voltage
1              11Kv
2              30Kv
3              33Kv
4              100Kv
5              500kv```

i know if values have alphabets it behave like that,but i need to use split voltage and order by asc or desc and append the last 2 letters.

note: only 2 letters appear at last of voltage

Sorry for forgot to mention datatype for voltage column. i use voltage as nvarchar(50)

Regards
Aravind
Posted
Updated 22-Jan-14 23:08pm
v2
Richard MacCutchan 23-Jan-14 3:37am

Your database field is wrong. You should store the voltage as a number not a string. Since every value is some number of volts or kVolts there is no need for the alphabetic suffix.

Solution 2

Griff is right, you should just store a number. This will work though

`Select Id, voltage from MyTable order by convert(int,replace(voltage, 'kv', ''))`

Fix your database if you can though, hacks like this slow the system down and tend to multiply

Note: I realised you needed to convert to int after the fact, you can thank the guy who gave answer 4 for that. I do think this is better, you don't need the CTE.
v2
Aravindba 23-Jan-14 4:06am

thanks a lot,it work thank u.....
Karthik_Mahalingam 23-Jan-14 4:11am

5! simple and neat.

Solution 4

Try this

```declare @table table
(
ID int ,voltage varchar(55)
)
insert into @table (ID,voltage)values (1,'33Kv')
insert into @table (ID,voltage)values (2,'11Kv')
insert into @table (ID,voltage)values (3,'100Kv')
insert into @table (ID,voltage)values (4,'30Kv')
insert into @table (ID,voltage)values (5,'500kv');

with T as
( select ID  ,voltage , CONVERT(int, REPLACE(voltage,'Kv','') ) as intv from @table    )
select ID,voltage from T order by intv asc```
Christian Graus 23-Jan-14 3:50am

Yeah, you are right. He needs to convert to int in order for it to sort properly
Christian Graus 23-Jan-14 4:09am

5 voted because I edited my answer after seeing something I missed in yours, and mine was marked as the answer :-)
Karthik_Mahalingam 23-Jan-14 4:20am

Thanks Christian
Aravindba 23-Jan-14 4:12am

thanxs for ur reply Graus solution work perfectly. and Sorry for forgot to mention datatype for voltage column. i use voltage as nvarchar(50) Actually i have fiedls in sql server for sample purpose here i give,so no need insert query.i need only select query with asc or desc for volage,anyway thank u for ur reply... keep go... nice i think this "select ID ,voltage , CONVERT(int, REPLACE(voltage,'Kv','')" it work for ur answer.
Karthik_Mahalingam 23-Jan-14 4:20am

Thanks Aravindba
Christian Graus 23-Jan-14 4:28am

Yes, the column MUST be nvarchar, or varchar. You don't need nvarchar for the letters kv and numbers.

Solution 1

The only sensible answer is: you can't, without a reasonable amount of work. You would be much, much better changing your data type from string to int or float and storing it as a numeric KV value. Then you can sort correctly without problems.

You can do it - by using SUBSTRING and CAST - but you will have to do it in every single query that you want to be ordered or compared with this column.

Trust me: it's a lot, lot easier to use a numeric column for a numeric value!
Aravindba 23-Jan-14 4:08am

Sorry for forgot to mention datatype for voltage column. i use voltage as nvarchar(50)
OriginalGriff 23-Jan-14 4:15am

And that's what I'm saying: don't. If you want to store numeric values, use numeric columns: otherwise everything you try to do with it has to be string based, or you have to convert to a numeric value every time.

I assume that your users type the KV value: what happens if they get it wrong? Do you check if the entry ends with "Kv", or do you assume it is right? What if they enter it as 10000v? or miss completely and enter 10Lv instead? If you just store the data in a string field, the error doesn't get spotted until someone needs to use the info: and which point it's too late to find out what the original user actual meant.
Use a floating point value that stores Kv values and verify them before they get anywhere near your database!
Aravindba 23-Jan-14 5:46am

thank,Actually user allow to select value form dropdownlist,so user cant enter any values,its predefined values in dropdownlist
OriginalGriff 23-Jan-14 5:56am

Then even more reason not to store them as strings! All you need is an integer value: your whole problem goes away *and* your database becomes smaller and faster to access...
Aravindba 23-Jan-14 23:28pm

Hai thank u for ur tip and tricks,i am not familer in database management system,here after i will follow. thanx a lot

Solution 3

Ideally, you should never store units and values together.

Now that this is done, you can remove the last two characters from Voltage column and sort the remaining.
Note that for this to work, you will need to ensure there is nothing other than KV as your unit specified.
Aravindba 23-Jan-14 4:08am

Sorry for forgot to mention datatype for voltage column. i use voltage as nvarchar(50),if we use nvarchar(50) we can store number and letters together.

Solution 5

If this is a fixed list, e.g. you use it to populate combobox in UI somewhere, populate the table is such way that ID matches your desired ordering.

```ID            Voltage
1              11Kv
2              30Kv
3              33Kv
4              100Kv
5              500kv```

If this data is not static or you cannot influence the value of ID, you need yo introduce another column which will contain the numeric value of the voltage so you can sort on it. You can keep the original values as well and use it for display.

```ID            DisplayText     Voltage
1              11Kv              11
2              100Kv            100
3              30Kv              30
4              33Kv              33
5              500kv            500```

If you really have to work with the original value, you need to order by this expression:

`CAST(SUBSTRING(Voltage, 1, LEN(Voltage)-2) AS INT)`