how to select ASC or DESC in sql server if values have alphabets ?

Question

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

SQL

select * from DBTree  order by Voltage asc

what i am getting is

VB

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

but what i need to get

VB

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 22-Jan-14 21:28pm

Aravindba

Updated 22-Jan-14 22:08pm

v2

Add a Solution

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.

5 solutions

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!

Posted 22-Jan-14 21:35pm

OriginalGriff

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

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.

Posted 22-Jan-14 21:40pm

Abhinav S

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.

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:

SQL

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

Posted 22-Jan-14 21:59pm

Tomas Takac

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Accepted Answer · 2014-01-22T21:39:00

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.

Posted 22-Jan-14 21:39pm

Christian Graus

Updated 22-Jan-14 21:51pm

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.

Karthik_Mahalingam · Accepted Answer · 2014-01-22T21:44:00

Solution 4

Try this

SQL

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

Posted 22-Jan-14 21:44pm

Karthik_Mahalingam

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.