Escaping the wildcards in queries LIKE

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 21 May 2004 12:49 pm

In most operating systems, use the * (asterisk) and? (question mark) to refer the wildcards.

We know that to query SQL using \"wildcards\" uses the LIKE, but the wildcards are different: for multiple characters, use the% and for a character is the _ (underline). Go to the examples:
Code: Select all
SQL> select * from tabela where campo like 'MAR_A';

CAMPO
------------------------------
MARIA
MARTA

SQL> select * from tabela where campo like 'MAR%A';

CAMPO
------------------------------
MARIA
MARTA
MARCIA
Now, say I want to fetch all the rows that had 20%-example:
Code: Select all
SQL> select * from tabela where campo like '20%';

CAMPO
------------------------------
20% JUROS
200% MEDIA
20% DESCONTO
error! Also brought 200%!!! It's not what we want! We will then \"escape\" that character of the query:
Code: Select all
SQL> select * from tabela where campo like '20~%%' escape '~';

CAMPO
------------------------------
20% JUROS
20% DESCONTO
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 08 Jul 2004 10:08 am

Just to complement: If you're in SQL * Plus, you can use the SET ESCAPE x (where x is a character). Give this character becomes the Joker.

Code: Select all
SET ESCAPE '$'
SELECT * FROM TABELA WHERE COL LIKE '$10%$'
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 11 guests