CODEWITHSUNDEEP
sqlsql-servert-sql
David Amankwa
David Amankwa

Reputation: 1

SQL involving columns with comma delimited strings and comparison

I am updating the requirement..Basically A string can have 2 or 3 or 4 decimal places decimal places instead of the two decimal places as show below.

Suppose I have a record with a comma delimited strings in a column name "Version"

Say we have a record (A) containing having these values in its "Version" column

Version=10.1.2.4, 10.4.3.4,11.6.0, (the column has comma delimited strings)

I am passing an sql parameter called @VersionCheck

IF @VersionCheck = 11.6.0 and Version does not have any value like 11.5.6 (Note the 11 matches with the 11 in 11.6.0)

I want to return the record

So here are some examples all comparing with the parameter @VersionCheck= 11.6.0

Version=10.1.2,10.4.3.45 return this record

Version=10.1.2,10.4.3,10.4.4.5,11.6.0 return this record (if we have a match 11.6.0 and there are no strings starting with 11.something that is not equal to 11.6.0(parameter passed) we return this record)

Version= 10.1.2,10.4.3,11.6.0.2 do not return this record (to check, take the string before the first decimal point, in this case is 11, however 11.6.0.2 and 11.6.0 is not the same so it should not match)

How do I write the sql query to have this logic in the where clause? If the parameter passed is @VersionCheck = 10.6.8 I do not want it to match if the strings in the comma delimited column has say 10.5.4 Basically take the characters before the first decimal and if they are equal (10=10), the rest of its values have to be the same else it should not match.

This is my solution below in sql server 2008 syntax

Note that @SU_Version is my parameter

and SuVersion.fieldValue is the column which has the comma delited string values like 10.1.2,10.3.4 etc

WHERE (CASE WHEN '%@SU_Version%' <> '' AND suVersion.fieldValue LIKE LTRIM('@SU_Version')
                THEN suVersion.fieldValue
                ELSE '%'
             END like CASE WHEN '%@SU_Version%' <> '' 
                THEN '%@SU_Version%' 
                ELSE '%'

        END

        AND

        (CASE WHEN LTRIM('@SU_Version') <> '' AND suVersion.fieldValue  LIKE SUBSTRING(LTRIM('@SU_Version'),1, CHARINDEX( '.',LTRIM('@SU_Version'))-1) 
                THEN suVersion.fieldValue
                ELSE'%'
             END not like CASE WHEN LTRIM('@SU_Version')<> ''
                THEN  SUBSTRING(LTRIM('@SU_Version'),1, CHARINDEX( '.',LTRIM('@SU_Version'))-1) + '%'
                ELSE '%'

        END
        AND 

        CASE WHEN LTRIM('@SU_Version') <> '' AND suVersion.fieldValue  LIKE SUBSTRING(LTRIM('@SU_Version'),1, CHARINDEX( '.',LTRIM('@SU_Version'))-1) 
                THEN suVersion.fieldValue
                ELSE'%'
             END not like CASE WHEN LTRIM('@SU_Version')<> ''
                THEN  SUBSTRING(LTRIM('@SU_Version'),1, CHARINDEX( '.',LTRIM('@SU_Version'))-1) + '%'
                ELSE '%'

        END)
      )

Upvotes: 0

Views: 492

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Since your design basically thwarts any attempts to use an index on the Version column anyway, this will give you rows where there is an exact match 

...
WHERE ',' + Version + ',' LIKE '%,' + @VersionCheck + ',%'

For the funky requirements you have, try this (this assumes all version strings will contain exactly two decimal places):

DECLARE @v TABLE(Version VARCHAR(MAX));

INSERT @v SELECT ('10.1.2,10.4.3')
UNION ALL SELECT ('10.1.2,10.4.3,11.6.0')
UNION ALL SELECT ('10.1.2,10.4.3,11.5.2');

DECLARE @SU_Version VARCHAR(32);
SET @SU_Version = '11.6.0';

DECLARE 
  @p1 VARCHAR(10),
  @p2 VARCHAR(10);

SELECT
  @p1 = PARSENAME(@SU_Version, 3),
  @p2 = PARSENAME(@SU_Version, 2);

SELECT Version = SUBSTRING(Version, 2, LEN(Version)-2) FROM 
(
  SELECT Version = ',' + Version + ',' FROM @v
) AS v WHERE 
(
  Version LIKE '%,' + @SU_Version + ',%' 
  OR
  Version NOT LIKE '%,' + @p1 + '.%,%'
  AND NOT
  (
    Version LIKE '%,' + @p1 + '.%,%'
    AND
    Version NOT LIKE '%,' + @p1 + '.' + @p2 + '.%,%'
  )
);

Upvotes: 3

Related Questions