Solved

# Problem with sorting integer values stored as text in database

Posted on 2003-03-20
Medium Priority
261 Views
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.

0
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

LVL 33

Expert Comment

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

hongjun
0

LVL 10

Accepted Solution

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

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,
yourintegervalue int NOT NULL,
DROP TABLE #MyTableInMemory

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

Good Luck
0

Author Comment

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

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

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

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
0

Expert Comment

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
0

Author Comment

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.

0

LVL 58

Expert Comment

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:

GaryC123
EE Cleanup Volunteer
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll