Geek asks and answers

More or less operators and versions

I have an MS SQL query that I am creating and I need to determine if a particular entry falls into the “new” or “old” category based on the version number.

There is a column called "VersionNum", and the old version is any version number from 2.75.99.99 and lower, and the new version is any version number from 2.76.00.00 and higher. The data type for the column is varchar (20).

I was thinking about taking a subset of the string from X.XX and doing it as a decimal. Then, as soon as all versions are transferred to the decimal number of X.XX, I can determine the old or new by doing a> or <in the case expression within select.

Would this be the right or ideological way to decide where version numbers fall? Or is there some other method. I just ask because I would suggest that something like this is needed somewhere else, and this is not a unique situation.

EDIT: Another problem. There are several versions that are not X.XX.XX.XX (2.76.00.00), and some of them are presented in the form: 8.00.00 and will be considered "old" versions. This confuses the system much more than I once thought. I received information that the 5-digit version (8.00.00) is considered old all the time. Therefore, when there are 5 digits, the version is old. Therefore, I think I would execute an operator similar to:

SELECT
    Old = CASE WHEN LEN(VersionNum) < 10 THEN 1
               WHEN CAST(SUBSTRING(VersionNum,1,4) AS DECIMAL(5,2)) < 1.52
                    AND LEN(VersionNum) >= 10 THEN 1
               ELSE 0
          END,
    New = CASE WHEN CAST(SUBSTRING(VersionNum,1,4) AS DECIMAL(5,3)) >= 1.52
                    AND LEN(VersionNum) >= 10 THEN 1
               ELSE 0
          END
FROM
    VersionTable

Is my logic correct? Or am I missing something. Inequality in lengths is very annoying.

+3
source share
3 answers

, , 4 :

DECLARE @x VARCHAR(20);

SELECT @x = '2.76.99.99';

SELECT 
 v = PARSENAME(@x, 4) * 1000000
   + PARSENAME(@x, 3) * 10000
   + PARSENAME(@x, 2) * 100
   + PARSENAME(@x, 1);

:

2769999

, , , /.

, , ?

UPDATE dbo.table SET col = col + '.00'
  WHERE LEN(x) < 10;

:

UPDATE dbo.table SET col = LTRIM(RTRIM(col)) + '.00'
  WHERE LEN(LTRIM(RTRTIM(x))) - LEN(REPLACE(LTRIM(RTRIM(x)), '.', '')) = 3;
+5

- smallint, int bigint , . , , .

, :

CREATE TABLE Versions
(MajorVersion int,
 MinorVersion int,
 Subversion int,
 Build int)

.

+1

If the numbers in the versions of the string with a zero letter are to the left, then you can simply use the string comparison: "2.75.99.99" <"2.76.00.00".

This will work as long as the format remains consistent across all versions.

0
source

More articles:


All Articles