• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

MS SQL: software version stored as string

I'm trying to do a software update against older version of software.  Unfortunately, the software version is stored in the SQL table as type "nvarchar" with data similar to:

3.4.2
3.12.1
3.2.6
4.1.2

I am trying to update the version 3 software and my query was
select *
from tblSoftware
where Version < "3.12.1"

which finds nothing because 3.2 is greater than 3.1.

So I guess i will need to convert the string to a number ?  or how would you go about this?

offhand, I would have to convert to three numbers (major, minor, other minor).  Surely there has to be an easier way than this.

Thanks,
Claud
0
Ameris
Asked:
Ameris
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Ephraim WangoyaCommented:

You have to convert them to individual numbers, then do a number comparison

You can then convert to version into an integer for easier comparison
(major * 10000 + Minor * 1000 + OtherMinor)
0
 
TomasPCommented:
You can parse the string left to right and covert each field between the dots as an integer.

There are hundreds of delimited string parsers available and by using the convert function you can easily put together a function that takes two versions as arguments and returns greater/less/equal .

The function will:
Covert each argument into an array of integers
Starting with the first integer of the 1st argument compare it to the first integer of the second argument
If the integers are equal, continue
else return greater/less status

I believe convert or cast will be for sql friends
0
 
Anthony PerkinsCommented:
>>There are hundreds of delimited string parsers available <<
And there is one built in T-SQL.  Try it this way.

select *
from tblSoftware
where CAST(PARSENAME(@Version, 3) AS integer) * 65536 +
	CAST(PARSENAME(@Version, 2) AS integer) * 256 + 
	CAST(PARSENAME(@Version, 1) AS integer) < 
	CAST(PARSENAME('3.12.1', 3) AS integer) * 65536 +
	CAST(PARSENAME('3.12.1', 2) AS integer) * 256 + 
	CAST(PARSENAME('3.12.1', 1) AS integer)

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Anthony PerkinsCommented:
Let me try that again:
select *
from tblSoftware
where CAST(PARSENAME([Version], 3) AS integer) * 65536 +
	CAST(PARSENAME([Version], 2) AS integer) * 256 + 
	CAST(PARSENAME([Version], 1) AS integer) < 
	CAST(PARSENAME('3.12.1', 3) AS integer) * 65536 +
	CAST(PARSENAME('3.12.1', 2) AS integer) * 256 + 
	CAST(PARSENAME('3.12.1', 1) AS integer)

Open in new window

0
 
Anthony PerkinsCommented:
Incidentally that becomes quite simpler if you wrap it in a function (sorry for the multiple posts):
CREATE FUNCTION dbo.udf_GetNumericVersion (
						@Version varchar(20)
						)
RETURNS integer

BEGIN

RETURN	CAST(PARSENAME(@Version, 3) AS integer) * 65536 +
	CAST(PARSENAME(@Version, 2) AS integer) * 256 + 
	CAST(PARSENAME(@Version, 1) AS integer)

END

You can then write the SELECT as:
select *
from tblSoftware
where dbo.udf_GetNumericVersion(Version) < dbo.udf_GetNumericVersion('3.12.1')

Open in new window

0
 
TomasPCommented:
If PARSENAME returns a 0 if there is no 3rd field it will work well  for all version numbers  including those like 1.2
A longer version number with more minor versions such as a patch will require a call to determine the number of fields 1st and adjust the addition numbers associiated with each field.
Parsing left to right without regard to less significant digits might be  bit faster but right to left and using addtion to create an integer is a great approach too where you can seperate the logic of the parse from the dispatch more easily. Nice
0
 
Anthony PerkinsCommented:
>>If PARSENAME returns a 0 if there is no 3rd field it<<
Actually it returns NULL.  But then you have ISNULL to take care of that as in:
CREATE FUNCTION dbo.udf_GetNumericVersion (
						@Version varchar(20)
						)
RETURNS integer

BEGIN

RETURN	ISNULL(CAST(PARSENAME(@Version, 3) AS integer), 0) * 65536 +
	ISNULL(CAST(PARSENAME(@Version, 2) AS integer), 0) * 256 + 
	ISNULL(CAST(PARSENAME(@Version, 1) AS integer), 0)

END

Open in new window

0
 
AmerisAuthor Commented:
sorry for the delayed response. we started a company-wide software upgrade (different software than question) and I've been swamped.  I'll try these out tomorrow.

Thanks,
Claud
0
 
AmerisAuthor Commented:
thanks everyone for the wonderful help.  my first question here.

Thanks,
Claud
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now