?
Solved

Problem with sorting integer values stored as text in database

Posted on 2003-03-20
10
Medium Priority
?
261 Views
Last Modified: 2008-03-17
My problem is that I have a text field in my database which stores both text and integer values. My problem is that I want to sort the integer values from this field in my asp code as it's supposed to be listed in a dropdown. Because it's stored as text it lists the value 1000 before the value 2 etc.

Is there any way to convert to integer in SQL, somehow order text fields as integers or do I have to manage it in the asp code? Splitting the field in two is not really an option at this point.

Thanks in advance
0
Comment
Question by:ishiguru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 8172628
Use this to convert into integer.
myvar = CInt(myvar)

hongjun
0
 
LVL 10

Accepted Solution

by:
lengreen earned 160 total points
ID: 8172662
if you can be sure the field you are using only contains numbers then you can use

SQL 200

create table TEST (
ID varchar(4)
)

insert into TEST (ID) values ('1000')
insert into TEST (ID) values ('2')
insert into TEST (ID) values ('100')
insert into TEST (ID) values ('22')


select * From TEST order by cast(ID as Int)


0
 

Expert Comment

by:AddeBC
ID: 8172669
Well there is one way that is kind of awkward but ill shhot...

I suppose you are Using MS SQL which you use Stored Procedures with.

So what i would do is create a stored procedure, that first sorts out all Integer values. This Values you Insert into a Temporary Table (wont exist on disc only in memory) from that you will select the integer values...

So when you create the temp table you specify an Integer field and thats where you insert the values and then get them with a standard sort...

 CREATE TABLE #MyTableInMemory(
          id int NOT NULL,
          blahhh varchar(30) NULL,
          tradaaa varchar(30) NULL,
          yourintegervalue int NOT NULL,
 DROP TABLE #MyTableInMemory


This is how you ccreate the Temorary table # means not to disc only memory...

Good Luck
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ishiguru
ID: 8172676
Well that does convert but I'm more looking for a type of conversion that can happen inside a SQL query. I'll post the code so you can see the problem.
I have a function which generates dropdrown's for unit types. For instance a memory dropdown which has 32,64 etc and a socket dropdown which has socket a, socket b etc.
--------------------------------------------------------
<!--#include file="DBfunc.asp"-->

<%
function SpecDropdown(array,categoryID,number)

dim i,specification,dropdown

      dropdown = "<select name='" & array(1,number) & "'><option> choose " & array(1,nubmer) & "</option>"
      query = "SELECT DISTINCT Specification.Specification FROM Specification WHERE Specification.SpecTypeID=" & array(0,nubmer) & " AND Specification.ProduktID IN(SELECT Produkt.ProduktID FROM Produkt WHERE Produkt.CategoryID=" & categoryID & ") ORDER BY Specification.Specification ASC"
      specification = Search(query)

      if IsArray(specification) then
       For i = 0 To UBound(specification,2)
        dropdown = dropdown & "<option value='" & specification(0,i) & "'>" & specification(0,i) & array(2,number) & "</option>"
      Next
      end if

      dropdown = dropdown & "</select>"

      SpecDropdown=dropdown

end function
%>
-----------------------------------------------------------
As you can see I'm searching for a conversion within SQL. But if that's not possible I guess I have to use Cint.
0
 

Author Comment

by:ishiguru
ID: 8172694
Well that does convert but I'm more looking for a type of conversion that can happen inside a SQL query. I'll post the code so you can see the problem.
I have a function which generates dropdrown's for unit types. For instance a memory dropdown which has 32,64 etc and a socket dropdown which has socket a, socket b etc.
--------------------------------------------------------
<!--#include file="DBfunc.asp"-->

<%
function SpecDropdown(array,categoryID,number)

dim i,specification,dropdown

      dropdown = "<select name='" & array(1,number) & "'><option> choose " & array(1,nubmer) & "</option>"
      query = "SELECT DISTINCT Specification.Specification FROM Specification WHERE Specification.SpecTypeID=" & array(0,nubmer) & " AND Specification.ProduktID IN(SELECT Produkt.ProduktID FROM Produkt WHERE Produkt.CategoryID=" & categoryID & ") ORDER BY Specification.Specification ASC"
      specification = Search(query)

      if IsArray(specification) then
       For i = 0 To UBound(specification,2)
        dropdown = dropdown & "<option value='" & specification(0,i) & "'>" & specification(0,i) & array(2,number) & "</option>"
      Next
      end if

      dropdown = dropdown & "</select>"

      SpecDropdown=dropdown

end function
%>
-----------------------------------------------------------
As you can see I'm searching for a conversion within SQL. But if that's not possible I guess I have to use Cint.
0
 

Author Comment

by:ishiguru
ID: 8172716
Sorry about the double post...dont know what went wrong there.

I hear you. The temporary table is indeed a solution. However I think performance wise it's quicker to sort the numbers in the asp code. Do you know of any sort function that takes integers stored in a array and sorts them by value?
0
 

Expert Comment

by:AddeBC
ID: 8172722
Well using teh Temp Table would make a conversion inside SQL... Nothing is ever written to disk...

The reason why this is the way to go is because you cant do a convertion unless all posts in that column are integers, else it will raise an exeption...

Temp tables is a neat way to do it... I luuv them =)

Sincerely
Adde
0
 

Expert Comment

by:AddeBC
ID: 8172729
Well using teh Temp Table would make a conversion inside SQL... Nothing is ever written to disk...

The reason why this is the way to go is because you cant do a convertion unless all posts in that column are integers, else it will raise an exeption...

Temp tables is a neat way to do it... I luuv them =)

Sincerely
Adde
0
 

Author Comment

by:ishiguru
ID: 8173020
I have worked around the problem using the jscript sort function. Maybe not the ideal solution but it works and is performance wise a good solution I think.

Thanks for all your replies.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9313540
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept Answer by lengreen

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question