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
AmerisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.