icon-arrow icon-check icon-mail icon-phone icon-facebook icon-linkedin icon-youtube icon-twitter icon-cheveron icon-download icon-instagram play close icon-arrow-uturn icon-calendar icon-clock
Werken bij Whitehorses
Blog 21/11/2019

How to escape characters in Oracle SQL

Oracle website

When you have to query an Oracle table and want to find rows containing a certain string, you can use wildcards in your WHERE clause. But what do you do if you need to search for rows containing a wildcard as a string? This question came up at one of our customers.

Integratiespecialist
Michel van Zoest /
Integratiespecialist

In a query searching for a string inside a column, you will use something similar to:

 

SELECT column
  FROM table
 WHERE column like '%string%';

But what do you do when you want to find a string containing a %-sign? Because this is the wildcard character, you will get all rows as a result from your query.
 

Luckily there is a solution for this problem. The ESCAPE keyword.

By using this keyword you can escape any character and search for strings containing wildcard characters.

Here is an example:

SELECT column
  FROM table
 WHERE column LIKE '%\%%' ESCAPE '\';

This way it will not see the 2nd %-character as a wildcard, but as a normal string character.

Geen reacties

Geef jouw mening

Reactie plaatsen

Reactie toevoegen

Jouw e-mailadres wordt niet openbaar gemaakt.

Geen HTML

  • Geen HTML toegestaan.
  • Regels en alinea's worden automatisch gesplitst.
  • Web- en e-mailadressen worden automatisch naar links omgezet.
Integratiespecialist
Michel van Zoest /
Integratiespecialist

Wil je deel uitmaken van een groep gedreven en ambitieuze integratiespecialisten? Stuur ons jouw CV!