Escaping Wildcards in Like

Escaping wildcards in LIKE

You can use the escape syntax

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character.

So you can do:

select * from property where name like '%\_%' escape '\';

NAME                 VALUE                                             
-------------------- --------------------------------------------------
max_width            90                                                

select * from property where name like '%\%%' escape '\';

NAME                 VALUE                                             
-------------------- --------------------------------------------------
taxrate%             5.20                                              

SQL LIKE query: escape wildcards

Yes. The simplest way is to use =, if that is appropriate.

Otherwise, the LIKE syntax includes an escape character. In your example, $ doesn't appear anywhere, so you could do:

where x like replace(y, '%', '$%') escape '$'

I believe that in all databases, the default is \, so you could also do:

where x like replace(y, '%', '\%') 

However, I prefer using the escape keyword so the intention is really clear.

How to programmatically escape wildcards in SQL LIKE?

Need to insert on each special character a escape character. As you said, you can stack a bunch of REPLACE functions:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

DECLARE @Replaced VARCHAR(100) = REPLACE(REPLACE(REPLACE(REPLACE(@Prefix, '%', '\%'), '^', '\^'), '_', '\_'), '[', '\[')

SELECT 
    Original = @Prefix,
    Translated = @Replaced

Result:

Original    Translated
My%Prefix_  My\%Prefix\_

Then use the ESCAPE clause for the LIKE to tell the engine that your character is the escape indicator:

SELECT * 
FROM Logs 
WHERE IDString LIKE @Replaced + '[-]%' 
ESCAPE '\'

---

You could also create a scalar function with the replaces:

CREATE FUNCTION dbo.ufnEscapeLikeSpecialCharacters(@Input VARCHAR(100), @EscapeCharacter CHAR)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(@Input, '%', @EscapeCharacter + '%'), '^', @EscapeCharacter + '^'), '_', @EscapeCharacter + '_'), '[', @EscapeCharacter + '[')
END

And then use it like:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

SELECT * 
FROM Logs 
WHERE IDString LIKE dbo.ufnEscapeLikeSpecialCharacters(@Prefix, '\') + '[-]%' 
ESCAPE '\'

T-SQL special characters to escape for LIKE operator wildcard search

It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').

DECLARE @Table1 TABLE
(
   Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);

INSERT @Table1(Column1)
VALUES
   ('abc%def'),
   ('abc_def'),
   ('abc[d]ef'),
   ('abc def'),
   ('abcdef');

DECLARE @p VARCHAR(32) = 'abc*]*';

DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');

SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;

Escape wildcard characters in LIKE condition

I eventually figured out that I can simply use prepared statements explicitly:

<?php
$query = array('OR' => array(
    array(
        'Post.title LIKE ?' => array(
            '%' . $this->escapeLike('100%') . '%'),
        ),
    ),
    array(
        'Post.title LIKE ?' => array(
            '%' . $this->escapeLike('red_apples') . '%'),
        ),
    ),
);

... or, whenever the DBMS does not have a default separator or we prefer to set ours:

<?php
$separator = '|';
$query = array('OR' => array(
    array(
        'Post.title LIKE ? ESCAPE ?' => array(
            '%' . $this->escapeLike('100%', $separator) . '%'),
            $separator
        ),
    ),
    array(
        'Post.title LIKE ? ESCAPE ?' => array(
            '%' . $this->escapeLike('red_apples', $separator) . '%'),
            $separator
        ),
    ),
);

My escapeLike() method is currently this (that was never the hard part):

/**
 * Escapes LIKE wildcard characters
 *
 * @param string $text
 * @param string $escape_character
 * @return string
 */
protected function escapeLike ($text, $escape_character='\\') {
    return strtr($text, array(
        '%'               => $escape_character . '%',
        '_'               => $escape_character . '_',
        $escape_character => $escape_character . $escape_character,
    ));
}

I implemented it in AppModel for simplicity (at least in my project).

This works on CakePHP/2.5 with the MySQL adapter (no idea of other scenarios).

Snowflake SQL LIKE ESCAPE with underscores and wildcards for one symbol

You can use regexp functions, or the split function to grab the third group of characters and check its length:

create or replace table T1(CD string);

insert into T1 (CD) values 
('SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO'),
('SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO'),
('SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO');

select *, split_part(CD, '_', 3) as LANGUAGE_CODE
from T1 where len(LANGUAGE_CODE) = 3;

How to escape wildcard characters in like clause?

In Hibernate 3 you can use the escape parameter to specify the escape char:

select foo from Foo as foo where foo.bar like '!%' escape '!'

I think that should work, although I have never tried it in practice.

SQL - How to use wildcard in LIKE as a normal character

try this :

select * from TABLE where VALUES like  '%[%]%'

---

Related Topics