10.1.4 Higher than 7.6.2 2

[bhp]

Hi am sure this is easy, can't figure it out. Just need to be able to find values higher or lower. In this case I have to varchar fields with software version in them. I need to know that "10.1.4" is higher than "7.6.2". Note the value can be either "10.1.1" or "10.1" or just "10"
Thanks for any pointers I though about using replace to remove the "." value but then if I compare 10 to 45 (10.0, 4.5) then 10 is less than 45
Thanks - James

 

[SkipVought]

Hi,

I need to know that "10.1.4" is higher than "7.6.2".

But it is not.

the character 1 colates before the character 7.

perhaps you ought to parse the strings and compare the first elements' CINT converted values. For instance...

dim a, b
a = split("10.1.4",".")
b = split("7.6.2",".")
select case CInt(a(0))-CInt(b(0))
  case 0

  case <0

  case >0

end select

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bhp]

Hi sorry if being dumb here I cannot find any sql command for split or CINT, many thanks - James

 

[SkipVought]

Sorry, I forgot I was in the SQL Server forum.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

sure this is easy, can't figure it out.

It's not easy. Basically, you want to treat a multi-part string as though it were a number. My recommendation would be to normalize your data in to 3 separate columns. After doing this, it is easy.

If you are unwilling, or unable to properly normalize the data, then you can do as Skip suggests. Take a look at the following example to get an idea on how to do this.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('10.1.4')
Insert Into @Temp Values('7.6.2')
Insert Into @Temp Values('7.6.0')
Insert Into @Temp Values('7.4.9')
Insert Into @Temp Values('8')

Select	*
From    (
         Select Data + Case Len(Data)-Len(Replace(data, '.',''))
                            When 2 Then ''
                            When 1 Then '.0'
                            When 0 Then '.0.0'
                            End As NormalizedData
         From   @Temp
         ) As A
Order By Convert(Int, ParseName(NormalizedData, 3)), 
        Convert(Int, ParseName(NormalizedData, 2)), 
        Convert(Int, ParseName(NormalizedData, 1))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bhp]

Hi thanks played around with and query returned values but I still cannot get it to only return values less than 7.6, i.e. it still returns 10.1.4 in your example. Am obvioudly looking at this the wrong way, thanks for help - James

 

[RiverGuy]

I agree with George, use three separate columns. For an example, if you actually look at the user interface for Visual Studio under their "Publish" tab, you will see that Major, Minor, Build and Revision are all listed in separate text boxes. You can even get these values separately from a .Net program without having to parse a string. I would assume most dev environments support this as well.

 

[bhp]

Thanks you have now lost me just looking for a sql query (or pointers) that will return values where 10.1 is higher than 7.6 (understand it is not) thanks - James

 

[RiverGuy]

The bottom line is that your database design does not support your business requirements very well at all. So you've got two choices: hack together an inefficient solution to meet your business requirements, or design the system to properly support those business requirements.

George gave you a pretty good solution. Did you try it?

 

[gmmastros]

RG,

My 'solution' doesn't really work too well.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

Guy: Doc, it hurts my head when I hit it with a hammer. What can I do?

Doc: Stop hitting your head with a hammer.

Guy: But....

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

Here's what you can do.

First, create this function in your database:

Create Function dbo.Hackify
	(@Version VarChar(30))
Returns VarChar(32)
As
Begin
Declare @a VarChar(10), @b VarChar(10), @c VarChar(10)

Set @Version = @Version + Case Len(@Version)-Len(Replace(@Version, '.',''))
                            When 2 Then ''
                            When 1 Then '.0'
                            When 0 Then '.0.0'
                            End

Select @a = ParseName(@Version, 3), 
       @b = ParseName(@Version, 2), 
       @c = ParseName(@Version, 1)

Return (Right('0000000000' + @a, 10) + Right('0000000000' + @b, 10) + Right('0000000000' + @c, 10))
End

Just copy/paste that to a query window and run it. You only need to do this once.

Now, you can use the function to compare your data. Like this...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('10.1.4')
Insert Into @Temp Values('7.6.2')
Insert Into @Temp Values('7.6.0')
Insert Into @Temp Values('7.4.9')
Insert Into @Temp Values('8')

Select   Data
From     @Temp
Where    dbo.Hackify(Data) < dbo.Hackify('7.6')

Note: If you have a lot of data in the table, this function can be quite slow. With smaller amounts of data, it will probably run just fine.

Basically, the Hackify function converts your string to a larger string. It first adds extra .0 's if need be. Then splits in to 3 parts and zero pads the data in each part so they are the same length. Once the string is 'normalized' like this, the comparisons will work properly.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

From gmmastros's original reply, could you not just modify the Order By clause as follows:

Order By Convert(Int, ParseName(NormalizedData, 3))* 10000 + 
        Convert(Int, ParseName(NormalizedData, 2)) * 100 + 
        Convert(Int, ParseName(NormalizedData, 1)) desc

 

[bhp]

Thanks all for help think I can pull together from the different additions, you guys are pretty smart

 

[SkipVought]

BTW, the assumption in the excellent gmmastro solution is, that in l.m.n, neither m nor n will ever exceed 99.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

No. In my most recent post, I assumed each part wouldn't exceed a 10 digit number. In fact.... with the last bit of code I posted, you can have alpha-numeric parts too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

I see. I was looking at the post previous to mine.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lost500]

Hey guys,
I was wondering what is the best way to keep your data straight. i have a number field that just keeps intergers and it thinks 10 comes befor 2 (which it does and i understand why) but i'm asking what is the best way to keep up with this? i can go in and add a zero infront of the 2 and then 02 comes before 10 but when i get passed 99 i'd have to go back and add another zero to keep it straight. is there some property or something to keep up with this or do i just need to add as many zero's as i think i will need but it looks wierd for my tract number (just a field name) to show as 0000000002 on a report. any advice would be great thanks guys good discussion.

 

[RiverGuy]

The best advice is to use a numeric data type to store the value. If you can't, your situation is a little easier to handle than the one above. You can simply order by the column, converted to INT. For example:

DECLARE @T1 TABLE (VCol VARCHAR(10))
INSERT INTO @T1 SELECT '2'
INSERT INTO @T1 SELECT '10'
INSERT INTO @T1 SELECT '1'

SELECT * FROM @T1 ORDER BY VCol

SELECT * FROM @T1 ORDER BY CONVERT(INT, VCol)