_ (underline) in consultation with LIKE

If your SGBD is SQL Server you need to escape the character:

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

  

LIKE

     

You can use the pattern matching wildcards as literal characters. To use a wildcard character as a literal character, include the wildcard character in brackets. The following table shows various examples of using the LIKE keyword and the wildcard characters [].

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

• Has 0 or more characters at start;
• Be followed by an occurrence of any character ;
• Own 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 match what was specified.

Another reference: (Joker - corresponds to a character) (Transact-SQL)

The underline has a meaning similar to the percent sign in the LIKE condition of SQL. Both are wild characters.

The difference is as follows:

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

For example, assuming we have a table with a column named word , and that the table has the following words added:

  

acata, category, ratchet, eschatology, mercator

Then the results of the queries will be as follows

... 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 applies to the three major DBMSs: SQL Server, Oracle and MySql.

I've come through this, I've taken my solution solution.

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