_ (underline) in consultation with LIKE

Question

I’m making a query where I need to return all information from a given table, which contains "_R_" in the nomenclature.

However, when using WHERE NM_CAMPAIGN LIKE '%_R_%' he returns me to other cases that have only "R_".

How else can I use that criterion?

Answer 1

Your case SGBD be the SQL Server you need to escape the character:

SELECT t.*
  FROM tabela t
 WHERE t.NM_CAMPAIGN LIKE '%[_]R[_]%'

---

LIKE

Determines whether a specific character string matches a specified pattern. A pattern can include normal and wildcard characters. During pattern matching, normal characters should match exactly the characters specified in the character string. However, wildcards can be matched to arbitrary fragments of the string. The use of wildcards makes the operator LIKE more flexible than the use of string comparison operators = and !=. If any of the arguments are not of the character string data type, the SQL Server will convert it to the character string data type, if possible.

---

_ (wildcard - corresponds to a character)

Use the underlined character _ to match any single character in a string comparison operation that involves matching patterns, such as LIKE and PATINDEX.

---

In the case of LIKE when you use the character _ is specifying the occurrence of any character, ie for the instruction %_R_% you are specifying that you want any result that:

• Possess 0 or more characters at the beginning;
• is followed by an occurrence of any character;
• Have the letter R;
• Have any character followed by anything at the end;

In your case I will list some possible results:

- 0R0;
- 000R0;
- 000R000;
- 0_R_0;

Results that would not be filtered by your search:

- R0;
- 0R;
- R000;
- 000R;
- A;
- R;

That’s why the results with _R_ were filtered, since they obey the rule, but are not the only ones that coincide with what was specified.

Answer 2

The underline has a meaning similar to the percentage sign in condition LIKE SQL. Both are wildcards.

The difference is as follows:

• % looking for zero or more occurrences of any character;
• _ looking for one or more occurrences of any character.

For example, suppose we have a table with a column called word, and that the table has the following registered words:

class, class, class, class, class

Then the results of the queries will be the following

... where PALAVRA like '%cat%'
-- retorna acata, categoria, catraca, escatologia, mercator

... where PALAVRA like '_cat_'
-- retorna acata

-- Combinando os dois agora:
... where PALAVRA like '%_cat_%'
-- retorna acata, escatologia, mercator

---

To search for the wildcard characters themselves, you must escape them with a backslash. Your Where clause should look like this:

... where NM_CAMPAIGN like '%\_R\_%'

This goes for the three main DBMS: SQL Server, Oracle and Mysql.

Answer 3

I’ve been there, I’ve come up with my solution of that answer.

SELECT * FROM mytable
WHERE LOWER(REPLACE(post_code_field, ' ', '')) = LOWER(REPLACE(?, ' ', ''))