7

Is there any way to perform a LIKE operation with XQuery in the same way as with SQL?

I wan't to construct some "startswith", "endswith" and "contains"-expressions.

Example of what I want to achieve:

for $x in /user where $x/firstname LIKE '%xxx' return $x
for $x in /user where $x/middlename LIKE 'xxx%' return $x 
for $x in /user where $x/lastname LIKE '%xxx%' return $x

Is there any way to achieve this in XQuery?

EDIT:

Got the answer to the question above. New problem:

Would there be any way to do this the opposite way around? I would like to run those queries with the sql equivalent NOT LIKE operator. Is this possible? It has to be in an FLWOR-expression

EDIT2:

Solved the problem. You can run fn:not(starts-with('123', '1')) and it returns false.

jorgen.ringen
  • 1,285
  • 5
  • 14
  • 20
  • I'm puzzled by the constraint "it has to be in a FLWOR expression". What's wrong with the simpler formulation `/user[matches(firstname, '.*xxx')]`? Simple path expressions are often more concise than FLWOR expressions. – Michael Kay Jan 26 '16 at 08:10
  • This is a useful question, which you seemed to have answered yourself, but not provided the output. Can you post your answers here please ? Were you able to use the syntax of the LIKE comparator string or did you have to translate it to an xQuery specific comparison string? – Ben May 18 '16 at 17:09

1 Answers1

13

XPath 2.0 and XQuery 1.0 (as standardized by the W3C) have regular expression support with the matches function http://www.w3.org/TR/xpath-functions/#func-matches:

/user[matches(firstname, 'xxx$')]

And of course there are functions like starts-with and contains (both in XPath 1.0/2.0), and ends-with (only in XPath 2.0) that might suffice.

Martin Honnen
  • 160,499
  • 6
  • 90
  • 110
  • @Martin Honnen: +1 Good answer. Edited because last sentence might be confusing. –  Feb 09 '11 at 16:44
  • 3
    XPath 1.0 equivalent of `ends-with` XPath 2.0 function: `substring($string, string-length($string) - string-length($pattern) + 1) = $pattern` –  Feb 09 '11 at 16:47
  • Would there be any way to do this the opposite way around? I would like to run those queries with the sql equivalent NOT LIKE operator. Is this possible? It has to be in an FLWOR-expression – jorgen.ringen Feb 10 '11 at 14:48
  • 1
    @JorgenR. inverse with `not()` boolean function. – Flack Feb 10 '11 at 15:07