3

In a QGIS Layer I encountered the following SQL expression and I'm wondering what the '#' and '%' symbols are for:

'#' + "IDs" + '#'  LIKE '%#' +  to_string( attribute( @atlas_feature ,'ID')) + '#%'

The Statement basically means, if the field "IDs" in the layer has the same numbers like the 'ID' field in the current atlas_feature layer, return true.

I don't understand the purpose of the '#' and '%' smybols. Why not simply write

"IDs" LIKE to_string( attribute( @atlas_feature ,'ID'))
6
  • 2
    % represents zero or more characters, re% finds re, red, and representationalism etc.. # represents any single numeric character 1#8 finds 108, 118, 188 etc.
    – Taras
    May 11, 2020 at 10:50
  • I'm not aware of # being a single character wildcard in QGIS (expressions, DB Manager SQL, etc). I can't seem to get it to work. _works on the other hand?
    – she_weeds
    May 11, 2020 at 10:59
  • OP, what does your IDs field look like? Is it a bunch of numbers delimited with # by any chance? (e.g. 1#2#3)
    – she_weeds
    May 11, 2020 at 11:02
  • @she_weeds they are a bunch of numbers, some are empty, maybe among the empty ones there are some 'spaces'
    – DGIS
    May 11, 2020 at 11:30
  • 2
    I see now. The # character is a numeric wildcard character in MS Access (thanks again Access for being special and weird). I don't think that would work in QGIS. If the IDs values were a series of numbers delimited by #, then that expression actually makes sense - if your atlas feature ID is 1 then the expression matches 1#2#3, 2#1#3, 3#2#1 but not e.g. 2#11#3 - but not otherwise.
    – she_weeds
    May 11, 2020 at 11:38

1 Answer 1

1

% is special character (only in second argument to LIKE expression) substituting for zero or more arbitrary characters.

# has no special meaning in SQL LIKE expressions in most databases.

The names used here suggest this expression uses it as its own special character. Probably attribute( @atlas_feature ,'ID') is a single ID, and column IDs is probably a list of IDs separated by #. The query checks that ID is in IDs list.

If it compared strings without #, the query would mistakenly find ID = 1 in list 111#222. By surrounding it with #, it makes sure only full IDs are compared. In example above, the condition would be '#111#222#' LIKE '%#1#%' and would not match. But if ID was 111, the condition would be be '#111#222#' LIKE '%#111#%' and this does match.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.