Why Does Using an Underscore Character in a Like Filter Give Me All the Results

Why does using an Underscore character in a LIKE filter give me all the results?

Modify your WHERE condition like this:

WHERE mycolumn LIKE '%\_%' ESCAPE '\'

This is one of the ways in which Oracle supports escape characters. Here you define the escape character with the escape keyword. For details see this link on Oracle Docs.

The '_' and '%' are wildcards in a LIKE operated statement in SQL.

The _ character looks for a presence of (any) one single character. If you search by columnName LIKE '_abc', it will give you result with rows having 'aabc', 'xabc', '1abc', '#abc' but NOT 'abc', 'abcc', 'xabcd' and so on.

The '%' character is used for matching 0 or more number of characters. That means, if you search by columnName LIKE '%abc', it will give you result with having 'abc', 'aabc', 'xyzabc' and so on, but no 'xyzabcd', 'xabcdd' and any other string that does not end with 'abc'.

In your case you have searched by '%_%'. This will give all the rows with that column having one or more characters, that means any characters, as its value. This is why you are getting all the rows even though there is no _ in your column values.

How to use an underscore character in a LIKE filter give me all the results from a column

Underscore (and percent) has a special meaning when used with LIKE, and means a wildcard for any single character. To workaround this, from the BigQuery documentation for LIKE:

You can escape "\", "_", or "%" using two backslashes. For example, "\%". If you are using raw strings, only a single backslash is required. For example, r"\%".

You may try double-escaping the underscore, if you intend for it be literal in your LIKE expression:

SELECT 
    date,
    creative_name,
    SUM(revenue)*2 AS spend
FROM `crate-media-group-client-data.DV360_ALL.GGLDV360BM_CREATIVE_*`
WHERE
    advertiser LIKE '%Topshop/Topman%' AND
    creative_name LIKE '%TS\\_AW19%' AND
    date = '2019-09-20'
GROUP BY 1,2 
ORDER BY
    creative_name;

Why does my query with LIKE '%\_' return all rows and not just those ending in an underscore?

Is your backslash not getting through to PostgreSQL? If you're passing the string through another layer that treats the backslash as an escape character (e.g. a Java String), then that layer may be removing the backslash, and you might need to escape your backslash for that layer.

Do you have any more single character usernames? If the backslash wasn't getting through to PostgreSQL then they would also match '_'

You might be able to try the ESCAPE clause: username LIKE '%!_' ESCAPE '!'

SQL Server Escape an Underscore

T-SQL Reference for LIKE:

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

For your case:

... LIKE '%[_]d'

Search '_' in SQL shows all records

_ is a wildcard that matches any character. You can choose another character to escape it. Say:

WHERE Name like replace(@Name, '_', '$_') + '%' ESCAPE '$'

That can get cumbersome, so you can just use different logic:

WHERE LEFT(Name, LEN(@Name)) = @Name

Unfortunately, this formulation prevents the use of an index.

using LIKE to get records that only has a specific character

In SQL Server, you can use:

SELECT TOP (1000) [PNO]
FROM [MYTABLE] 
WHERE PNO like  '%~%[^~]'

In other databases, you would likely use a regular expression, such as `'~.*[^~]$'.

As a last recourse, you can do this with like and not like:

WHERE PNO LIKE '%~%' AND PNO NOT LIKE '%~';

How can I use underscores in LIKE conditions in Tivoli?

The underscore matches a single character, unless it is escaped. Try:

select NODE_NAME from NODES where NODE_NAME like '%__SQL' escape '_'

How to use like condition in query to fetch strings that contains % symbol

Have you used the "_" underscore character in SQL query?
This may help to resolve your issue.

    select * from myTableName where details like 'abc%_%'
or 
    select * from myTableName where details like 'abc%_'
or 
    select * from myTableName where details LIKE '%abc\%%' ESCAPE '\'
or
    select * from myTableName where details LIKE 'abc\%%' ESCAPE '\'

All the above queries will solve your issue, use the appropriate query based on your application need and requirement.

Reference: Use Underscore character in wild card charecter of Like query gives me all table result

fulltext search over data with underscore

Kees C Bakker is 100% correct, but if you just wanted to get the results you require without all of the steps.
The quick/dirty way to do so would be change your search to be a like...

Select * from MyAmazingTable where Name like '%A3L'

The % in this case would represent whatever comes before and make sure the last 3 characters are A3L.
Which will give you the results that you are looking for.

---

Related Topics