[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Problem with sorting integer values stored as text in database

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
ishiguru
Asked:
ishiguru
1 Solution
 
hongjunCommented:
Use this to convert into integer.
myvar = CInt(myvar)

hongjun
0
 
lengreenCommented:
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
 
AddeBCCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ishiguruAuthor Commented:
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
 
ishiguruAuthor Commented:
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
 
ishiguruAuthor Commented:
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
 
AddeBCCommented:
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
 
AddeBCCommented:
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
 
ishiguruAuthor Commented:
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
 
GaryCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now