Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sorting Strings Numerically

Posted on 2006-11-06
6
Medium Priority
?
661 Views
Last Modified: 2013-12-09
I have a field in my database that I need to sort. The field is a string but I need to sort numerically.

Version=9.5;Prior=NONE;
Version=9.2a;Prior=NONE;
Version=13.3;Prior=USS;
Version=13.1b;Prior=UAN;

How can I grab the version number and sort it so instead of:

9.5
9.2a
13.3
13.1b

I get:

13.3
13.1b
9.5
9.2

Someone suggested to me:

OK, here's one way...

Example "Builds" table:

Build
------
9.5
9.2a
13.3
13.1b
2.0
2.0a
2.0b
2.1
2.1a
2.1b

Querying the Builds table, sorting by the major build value:

SELECT
     Build,
     CAST(PARSENAME(Build,2) AS INT) AS MajorVersion,
     PARSENAME(Build,1) AS MinorVersion
FROM Builds
ORDER BY MajorVersion, MinorVersion

Outputs the following:

Build      MajorVersion MinorVersion
---------- ------------ ------------
2.0        2            0
2.0a       2            0a
2.0b       2            0b
2.1        2            1
2.1a       2            1a
2.1b       2            1b
9.2a       9            2a
9.5        9            5
13.1b      13           1b
13.3       13           3

I am using Firebird not SQL Server.

I am low on points but need help please. Thanks!
0
Comment
Question by:yourbudweiser
[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
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 17884712
what version of firebird are you using?
0
 
LVL 3

Author Comment

by:yourbudweiser
ID: 17884729
Hi NickUpson, we are using Firebird 1.5
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 17884790
I don't have time to write the code but I can give you the information necessary for you to do it

create an additional field in the table to hold the numeric form, say vernum
write an stored procedure that uses substring to step through the field, 1 char at a time until hits a non-numeric (skip/handle decimal point) building up the numeric form and save it.
then select and sort by the numeric form

if you are going to need it again, put the code in an trigger so that vernum is kept updated as data is updated/inserted
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 3

Author Comment

by:yourbudweiser
ID: 17884848
that is the general idea I received above but I am not sure how to extract just the version number from the string and I am not experienced to write stored procedures. Is there no quick and dirty way to do this in the sql statement?

Version=9.7;PriorCompany=NONE;Password=123abc;userName=JAZARETPC;testServer=ONLINE;
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 160 total points
ID: 17885010
there is no quick & dirty way as the position & length of the part you want varies

if it was in the same place you could do it, you need something like

for select ... into ... :myvar
do
begin
  i = 0;
  vernum = 0;
  while (substr(myvar, i, 1) < '0' and substr(myvar, i, 1) > '9')
    vernum = (varnum * 10) + substr(myvar, i, 1)
  update mytable set vernum = :vernum where ...
end
0
 
LVL 3

Author Comment

by:yourbudweiser
ID: 17885106
thanks for the help, this is too comlicated for me as I cannot modify the database by adding stored procedures or additional fields in the table. Thanks for taking a look though.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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