Click here to Skip to main content
14,735,205 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)
Pls reply me asap

Regards
Aravind
Posted
Updated 22-Jan-14 23:08pm
v2
Comments
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.

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
Comments
Aravindba 23-Jan-14 4:06am
   
thanks a lot,it work thank u.....
Karthik_Mahalingam 23-Jan-14 4:11am
   
5! simple and neat.
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
   
Comments
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.
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!
   
Comments
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
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.
   
Comments
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.
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)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900