Sql, Compged, Min and blanks

Question

I'm comparing 4 strings using compged in sql here is an extract:

MIN(compged(a.string1,b.string1),
compged(a.string1,b.string2),
compged(a.string2,b.string1),               
compged(a.string2,b.string2)) < 200

Unfortunately there are times that a string from set a and a string from set b is blank/empty, this means compged resolves to 0 and the min found is 0. Is there a way to modify so that comparing two blank strings gives a value greater than 200 or something?

Thanks in advance

Its part of a proc sql statement in SAS – matthew May 07 '15 at 10:06 — matthew, May 07 '15 at 10:06

score 4 · Answer 1 · answered May 07 '15 at 10:34

You can calculate new variables to handle that situation (both compared variables are blank) and use them inside the MIN() function:

case
    when (missing(a.string1) and missing(b.string1)) then 300
    else compged(a.string1,b.string1)
end as compged_11,
/* do the same for combinations 12, 21 and 22 */
MIN(calculated compged_11, 
    calculated compged_12, 
    calculated compged_21, 
    calculated compged_22) < 200

score 0 · Answer 2 · answered May 07 '15 at 10:32

The quick and dirty option is to wrap each string with a different 200char string in case the string is null or the length is 0 (as empty strings aren't always referenced as NULL) So a.string1 = 200*'Z', b.string1 = 200*'X'.....

Or better even, to wrap each call with checks so if a.string1 is null or is empty, then return the length of the other string. And if both are empty, then return 1000 so the record is removed by the where clause.

You can also add a prefix - 'A' to all strings. This will ensure tht there are no empty strings, and will not change the distance. But you still need to weed out cases where both strings are empty.

Sql, Compged, Min and blanks

2 Answers2