Comparison of Strings in sqlServer

Question

Good morning. I have two strings defined : '2.9.15' and '2.9.16'; I need to compare them and tell you which one is the biggest. I don’t know how to do.

Answer 1

If this is a column of one of your tables, you can use the Max() within your select to take the most value, this way:

Select Max(sua_coluna) from sua_tabela

If the comparison is made between two strings, there is no 'specific function' that does this, but you can use something like:

CASE
  WHEN String1 = String2 THEN 0 --Os valores são iguais
  WHEN String1 < String2 THEN String2
  WHEN String1 > String2 THEN String1
  ELSE NULL --Uma das strings tem o valor nulo
END

Answer 2

I don’t know exactly what your need is.

So it would be an option for this case.

DECLARE @STR1 VARCHAR(20)
DECLARE @STR2 VARCHAR(20)

SET @STR1 = '2.9.15'
SET @STR2 = '2.9.16'

IF CAST( REPLACE( @STR1,'.', '' ) AS NUMERIC) > CAST( REPLACE(@STR2,'.','' ) AS NUMERIC) 
   PRINT 'A PRIMEIRA' 
ELSE 
 IF CAST( REPLACE( @STR1,'.', '' ) AS NUMERIC) < CAST( REPLACE(@STR2,'.','' ) AS NUMERIC) 
      PRINT 'A SEGUNDA'
  ELSE 
      PRINT 'SAO IGUAIS'

Answer 3

Direct version comparisons like string are unreliable. For example, "2.1.20" is more current than "2.1.9" but, when directly comparing the two values, the most current value returned is "2.1.9".

-- código #2
declare @V1 varchar(10), @V2 varchar(10);
set @V1= '2.1.9';
set @V2= '2.1.20'

IF @V1 > @V2
  SELECT @V1 + ' mais atual do que ' + @V2
else IF @V2 > @V1
       SELECT @V2 + ' mais atual do que ' + @V1
     else SELECT @V1 + ' é igual a ' + @V2;

One solution is to convert the string value to numeric. Assuming that the version number is composed of 3 parts and that each part can vary from 0 to 999, it follows a solution in which the version string is converted to numerical value using the formula: the first part is multiplied by 10 6, the second by 10 3 and the third by 10 0. For example:

"2.1.30" = 2 * 10^6 + 1 * 10^3 + 30 = 2.000.000 + 1.000 + 30 = 2.001.030
"2.12.0" = 2 * 10^6 + 12 * 10^3 + 0 = 2.000.000 + 12.000 + 0 = 2.012.000

The code goes like this:

-- código #1 v3
declare @versãoA varchar(10), @versãoB varchar(10);
set @versãoA= '2.1.9';
set @versãoB= '2.1.20';

-- converte valor string em numérico
declare @versãoA_n int, @versãoB_n int;
set @versãoA_n= cast(parsename(@versãoA, 3) as int) * power(10, 6) + 
                cast(parsename(@versãoA, 2) as int) * power(10, 3) + 
                cast(parsename(@versãoA, 1) as int);
set @versãoB_n= cast(parsename(@versãoB, 3) as int) * power(10, 6) + 
                cast(parsename(@versãoB, 2) as int) * power(10, 3) + 
                cast(parsename(@versãoB, 1) as int);

-- retorna o mais recente. Se forem iguais, retorna NULL
SELECT Recente= case when @versãoA_n > @versãoB_n 
                     then @versãoA
                     else case when @versãoA_n < @versãoB_n 
                               then @versãoB
                               else NULL
                          end
                end;

The above solution uses the function PARSENAME to obtain the version numbering parts. Another (more laborious) way to obtain the parts is by using the SUBSTRING and CHARINDEX functions.

Code #1 assumes that versions will always have 3 parts. However, if the part number is variable, another approach is needed.