5

How can we escape a percent character in a LIKE expression in QGIS?

For example, if I have the following column:

123% , 15% , albert, greg, 15%

How do I select all the values with % in it without changing the field?

It seems possible to do this in ArcGIS, according to this answer but I have not found an equivalent solution with QGIS.

I have tried escaping with %%, \%, \\\%, [%], ^%, etc. but without success.

[EDIT]

I actually truly was wondering if it is possible to escape the % (and _) character, rather than to use perfectly (valid) work-around. The example provided was but for illustration.
Any ideas?

|improve this question
  • i tried the solution mentioned here ; gis.stackexchange.com/a/87888/25139 and qgis created the following query "FIELDNAME" ILIKE '%%%' but it doesn't do the trick. Could it be a bug ? – gisnside Oct 8 '16 at 1:59
  • As per your edit - agreed, really you need an answer to using a standard escape instead of workaround. If no answer appears here, worth filing a bug report! – Simbamangu Oct 8 '16 at 7:10
6

This was a missing piece in QGIS in the past. But as of (upcoming) QGIS 2.14.8 and 2.18, \ has been introduced as escape char for LIKE and friends.

Just have a look at the builtin documentation for LIKE which ships with examples for the usage.

It is already possible to get these versions as pre-release versions (qgis-dev / qgis-rel-dev on OSGeo4W). As such, it is recommended to install them and test them so the developers can get an early feedback in case of problems.

For an approach compatible with earlier versions of QGIS, have a look at the very inventive answer by gisnside.

|improve this answer
  • 1
    Thanks Matthias ! I admit this one had me occupied this morning : I looked for any SQL tricks, like with the ESCAPE keyword (techonthenet.com/sql/like.php), I also checked replacing with %% or [%] et / or \ but it didn't work either. I was very surprised not to see a build-in solution, so I found one :) Happy to see someone has corrected that in any case. – gisnside Oct 8 '16 at 20:03
5

You can virtually replace your % in the value coming from the source field with another ascii sign or any string value, it will allow you to select the replacing percentage value with the following code : 

replace( "FIELDNAME",'%','€' )  LIKE '%€'

This way, you don't have to modify your source values.

You could of course choose a unique value to make sure you don't select anything else, it would also work to select % values : 

replace( "FIELDNAME",'%','_mytailorisrich' ) LIKE '%_mytailorisrich'
|improve this answer
  • It works great! – Victor Oct 8 '16 at 6:45
1

How about:

 right("FIELDNAME", 1) = '%'

or

 right("FIELDNAME", 1) like '%'

Just trying as I don't have values like yours - but it validates with QGIS...

|improve this answer
  • First one works, although '1' is sufficient instead of '2'. There is no space or commas in the values. The second one does not work since the % is used as a wild card (hence the issue) and it therefore selects all. – Victor Oct 8 '16 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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