What does '%,' and '.-1,%' and ',%' or '%,' mean in SQL/Oracle?

Question

the code specifically I am referring to is:

AND (
(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || 
    CASE WHEN log.subtype is null 
    THEN ' ' 
    ELSE log.subtype 
    END || ',%')
OR (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%')
OR (to_char(log.logtypeid) LIKE 
    CASE 
    WHEN to_char('~[gpv:lt]') = '-1' 
    THEN '%' 
    ELSE ','||to_char('~[gpv:lt]')||',' 
    END)  
)

Any clarification would be great. Thank you!

Answer 1

Let's start with this expression:

(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || 
    CASE WHEN log.subtype is null 
    THEN ' ' 
    ELSE log.subtype 
    END || ',%')

It is an example of this idiom:

','||a||',' LIKE '%,'||b||',%'

in which a is your lt parameter, and b a "type dot subtype" string. This comparison can be used whenever you have an a string that is a comma-separated list of values, and a b string that is a single value, and you want to know whether the list a contains the value b.

To see why it's written that way, first look at this attempt to do it more simply:

a LIKE '%'||b||'%'

We take b, put a wildcard at the front and back, and match a. This will be true if a is for example 1,2,3 and b is 2. It will also unfortunately be true if a is 12,34,56 and b is 2. LIKE doesn't do comma-separated list parsing, just string matching.

So next you might try this:

a LIKE '%,'||b||',%'

Now if b is 2, the pattern is %,2,% - which will match any string containing ,2, so it is true for a=1,2,3 and false for a=12,34,56. Unfortunately it's also false for a=2,3,4 because the 2 doesn't have a comma before it and false for a=0,1,2 because the 2 doesn't have a comma after it.

For the next improvement there are 2 ways to go. You can use separate pattern cases to match b at the beginning, middle, and end of a (if you go this way, using a regular expression will help make it readable!)

The other way is to modify a to match the existing pattern. We didn't match 0,1,2 or 2,3,4 because the first element of the list and the last element of the list aren't surrounded by commas. But if we add a comma to the beginning of a before matching, then the first element of the list will be surrounded by commas! And add another comma to the end of a to make sure the last element is also surrounded by commas.

','||a||',' LIKE '%,'||b||',%'

Now when a is 0,1,2 and b is 2, the LIKE expression becomes:

',0,1,2,' LIKE '%,2,%'

which is a match! The first wildcard absorbs the ,0,1 and the ,2, is found. The last wildcard matches a zero-length substring at the end, which is allowed.

Answer 2

% is known as a Wildcard character. More information here.

Answer 3

Those -1,% are string literals and are used to build a LIKE condition based on several contants and the values of some columns. The statement shows a basic misunderstanding on how to use string literals in Oracle.

Take for example this condition:

','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%'

The to_char('~[gpv:lt]') is totally useless because it simply converts the string constant '~[gpv:lt]' to ... a string. So that part could be simplified to:

',~[gpv:lt],' LIKE '%,' || to_char(log.logtypeid) || '.-1,%'

which essentially says:

Compare the string constant ',~[gpv:lt],' with the result of the concatenation of the following

1. the constant '%,'
2. the value of the column log.logtypeid converted to a varchar
3. the constant '.-1,%'

So, assuming log.logtypeid contains the value 42 this generates the condition

',~[gpv:lt],' LIKE '%,42.-1,%'

This could only match if the column log.logtypeid contained a value like ~[gpv:lt]. Which would be a bit confusing as columns named "id" usually don't contain "structured data" like that.

If I had to guess, I'd say the data model is heavily de-normalized and those columns store comma separated, structured data (maybe even structured key/value pairs).

The other conditions do something similar.