Need Help Sorting

Question

Post reply

Need Help Sorting

Rich Cowan

Old Hand

Points: 336
More actions
December 14, 2006 at 1:53 pm

#118245

I need help sorting a file Version column.
Here are some examples of possible values in this field.
6.0,12.1234
6.0.6
5,1,3,4
Version 6
Each one of these is a possible value. I need to develop a way to make sure that they are sorted in the proper order. Like this:
5,1,3,4 then
Version 6
6.0.6
6.0.12.1234
This is currently a big issue that I need to resolve fast. Any ideas would be greatly appreciated.
I'm using SQL Server 2k5.
Thanks!
R.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

If you could explain the logic of your sorting in plain English...

_____________
Code for TallyGenerator

DaveItz SSCrazy Points: 2146 More actions · Answer 2

I'm not sure what you mean by the commas but you should probably look into the ParseName function.

Rich Cowan Old Hand Points: 336 More actions · Answer 3

The values I provided as examples are truly real discovered values. Since we discover over 75,000 .exe's across our cient via SMS, you can only imagine the number of versions and the variations that come along with that.

What I really need is a udf that will correctly sort all values found. While 6.0.6 is less than 6.0.12, in Europe, 5,1,3,4 is less than 5,1,14,5; and for non-standard SW Vendors, Version 6 is truly less than Version 6 R54.

I'm sorry that I can't explain it any better than that. Let me nkow what part you don't understand and I will take another shot at it.

Thanks in advance.

R.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 4

There is definitly not a pretty way to do what you want. My advice is to split by, then strip out, all non-numeric characters, cast the remaning numeric characters as integers and then sort by those.

By non-numeric I mean specifically [0-9], not what returns true from ISNUMERIC()

SQL guy and Houston Magician

Sergiy SSC Guru Points: 110208 More actions · Answer 5

I support Robert.

What you really need is UDF to split your strings into proper set of Version, Subvirsion, etc.

Then you may order by parts.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Rich,

If your version numbers will never have more than 4 parts, here's a good start without a UDF... the first two sections of the code below are just to setup some test data... the last section is where the rubber meets the road... of course, you will need to change the table name and, perhaps, some column names...

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#VersionTest') IS NOT NULL

DROP TABLE #VersionTest

--===== Create some data to test with

CREATE TABLE #VersionTest(RowNum INT IDENTITY(1,1), Version VARCHAR(50))

INSERT INTO #VersionTest (Version)

SELECT '6.0,12.1234' UNION ALL

SELECT '6.0.6' UNION ALL

SELECT '5,1,3,4' UNION ALL

SELECT 'Version 6' UNION ALL

SELECT 'Version 6 R54' UNION ALL

SELECT 'Version 6R5'

--===== Demo how to do the sort

SELECT d.RowNum,

REVERSE(Version) AS Version

FROM (--Derived table "d" cleans and reverses the data to prep for parsename

SELECT RowNum,

REVERSE(

REPLACE(

Version

,',','.')

,'Version ','')

,'R','.')

,' ','')

) AS Version

FROM #VersionTest

) d

ORDER BY STR(REVERSE(PARSENAME(d.Version,1)),4),

STR(REVERSE(PARSENAME(d.Version,2)),4),

STR(REVERSE(PARSENAME(d.Version,3)),4),

STR(REVERSE(PARSENAME(d.Version,4)),4)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Of course, if you want to go with total formatting of the version column for ease of sorting in the future...

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#VersionTest') IS NOT NULL

DROP TABLE #VersionTest

--===== Create some data to test with

CREATE TABLE #VersionTest(RowNum INT IDENTITY(1,1), Version VARCHAR(50))

INSERT INTO #VersionTest (Version)

SELECT '6.0,12.1234' UNION ALL

SELECT '6.0.6' UNION ALL

SELECT '5,1,3,4' UNION ALL

SELECT 'Version 6' UNION ALL

SELECT 'Version 6 R54' UNION ALL

SELECT 'Version 6R5'

--===== Demo how to do the sort

SELECT d.RowNum,

REPLACE(STR(REVERSE(ISNULL(PARSENAME(d.Version,1),'0')),4),' ','0')+'.'

+REPLACE(STR(REVERSE(ISNULL(PARSENAME(d.Version,2),'0')),4),' ','0')+'.'

+REPLACE(STR(REVERSE(ISNULL(PARSENAME(d.Version,3),'0')),4),' ','0')+'.'

+REPLACE(STR(REVERSE(ISNULL(PARSENAME(d.Version,4),'0')),4),' ','0')

FROM (--Derived table "d" cleans and reverses the data to prep for parsename

SELECT RowNum,

REVERSE(

REPLACE(

Version

,',','.')

,'Version ','')

,'R','.')

,' ','')

) AS Version

FROM #VersionTest

) d

ORDER BY STR(REVERSE(PARSENAME(d.Version,1)),4),

STR(REVERSE(PARSENAME(d.Version,2)),4),

STR(REVERSE(PARSENAME(d.Version,3)),4),

STR(REVERSE(PARSENAME(d.Version,4)),4)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 8

Create a surrogate column that holds uniform versioninfo that is updated by insert,update trigger. You can use code from previous posts for transformation.

Unless the table is very small in which case any of proposed solutions will work fine.

Rich Cowan Old Hand Points: 336 More actions · Answer 9

Jeff, thanks for the code set. I will look at it over the weekend. I like the principle of it, but one problem is that I would need to generically replace char values (or remove them) where you replace them literally.

I have hit up our SQL DB team as well as our .Net programmers here to see if they can develop a function that I could use. Since 2k5 is so well integrated, I may find better options (or at least more elegant ones) from them. The one problem I can see immediately with a udf in SQL is the number of records I am processing. I have over 75k discovered .exe's with countless variations of the version value looking across 25k machines for an ever-growing list of apps to determine compliance for. While my curretn product list to determine compliance is only at 4, that is only my test population.

Just for the knowledge, my methadology is like this:

Maintain a product list with filename, version, path and standard (<,<=,=,>=,>,any, or none).

Gather a list of all file versions discovered for that particular product and place them into a temp table (this is where my order by functino is needed as I need to sort the version of the file so that I can "RANK" them and then compare the file versino found on each box to the "RANKED" standard and determine if they are compliant or not.

Tag the machine compliant and go to the next machine.

Hope this makes sense. There is some other work involved. I would be willing to post my stored proc if you have any interest in looking at it.

I'm truly needing this sort function to save the world.

Thanks again for all of your help and insight.

R.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

So... how many rows must you examine (do this for)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Here... shoe-horn a call to this function into my previous code to get rid of all but the required "pattern" of characters...

CREATE FUNCTION dbo.CleanData(@sText AS VARCHAR(8000),@pattern varchar(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Result AS VARCHAR(8000)

SELECT @Result = ISNULL(@Result,'') + SUBSTRING(@sText, N, 1)

FROM dbo.TALLY

WHERE N <= LEN(@sText)

AND SUBSTRING(@sText, N, 1) LIKE @pattern --Things like '[0-9,.]'

RETURN @Result

END

GO

And, if you don't have a Tally table, now's the time to build one...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)